몇번 작성하다 실패해서 여쭤 봅니다.
WITH t AS
(
SELECT 'R1' AS RR,1 AS SEQ,'4171745' AS BID FROM DUAL
UNION ALL SELECT 'R1',2,'' FROM DUAL
UNION ALL SELECT 'R1',3,'4171846' FROM DUAL
UNION ALL SELECT 'R1',4,'4171846' FROM DUAL
UNION ALL SELECT 'R1',5,'' FROM DUAL
UNION ALL SELECT 'R1',6,'' FROM DUAL
UNION ALL SELECT 'R1',7,'' FROM DUAL
UNION ALL SELECT 'R1',8,'8000340' FROM DUAL
UNION ALL SELECT 'R1',9,'8000340' FROM DUAL
UNION ALL SELECT 'R1',10,'' FROM DUAL
UNION ALL SELECT 'R1',11,'' FROM DUAL
UNION ALL SELECT 'R1',12,'4120554' FROM DUAL
UNION ALL SELECT 'R1',13,'4120554' FROM DUAL
UNION ALL SELECT 'R1',14,'' FROM DUAL
UNION ALL SELECT 'R1',15,'4171979' FROM DUAL
UNION ALL SELECT 'R1',16,'4171979' FROM DUAL
UNION ALL SELECT 'R1',17,'' FROM DUAL
UNION ALL SELECT 'R1',18,'' FROM DUAL
UNION ALL SELECT 'R1',19,'4120551' FROM DUAL
UNION ALL SELECT 'R1',20,'4120551' FROM DUAL
UNION ALL SELECT 'R1',21,'' FROM DUAL
UNION ALL SELECT 'R1',22,'' FROM DUAL
UNION ALL SELECT 'R1',23,'' FROM DUAL
UNION ALL SELECT 'R1',24,'' FROM DUAL
UNION ALL SELECT 'R1',25,'4198741' FROM DUAL
UNION ALL SELECT 'R2',1, '8000340' FROM DUAL
UNION ALL SELECT 'R2',2,'' FROM DUAL
UNION ALL SELECT 'R2',3,'' FROM DUAL
UNION ALL SELECT 'R2',4,'4120554' FROM DUAL
UNION ALL SELECT 'R2',5,'4120554' FROM DUAL
UNION ALL SELECT 'R2',6,'' FROM DUAL
UNION ALL SELECT 'R2',7,'4171979' FROM DUAL
UNION ALL SELECT 'R2',8,'4171979' FROM DUAL
UNION ALL SELECT 'R2',9,'' FROM DUAL
UNION ALL SELECT 'R2',10,'8005340' FROM DUAL
)
SELECT * FROM T
RR에 대한 구간 정보를 구하려고합니다.
SEQ는 순번입니다 (예제를 잘못 만들어서 문자가 되었네요, 숫자로 변경했습니다)
순번대로 bid 처음과 끝을 구려고 하는데
bid는 시작 다음 나오는 bid는 종료 입니다.
종료된 bid와 시작 bid는 같은 id입니다.
중간 null값으로 시작 종료를 구하지 못하고 있어요~
WITH FIN AS
(
SELECT 'R1' AS RR,1 AS SEQ, '4171745' AS BID, 'A' AS SID, 'AA' AS SSID, '4171745' AS STID, '4171846' AS EDID,1 AS DR_SEQ FROM DUAL
UNION ALL SELECT 'R1',2,'','A','AA','4171745','4171846',1 FROM DUAL
UNION ALL SELECT 'R1',3,'4171846','A','AA','4171745','4171846',1 FROM DUAL
UNION ALL SELECT 'R1',4,'4171846','B','BB','4171846','8000340',1 FROM DUAL
UNION ALL SELECT 'R1',5,'','B','BB','4171846','8000340',1 FROM DUAL
UNION ALL SELECT 'R1',6,'','B','BB','4171846','8000340',1 FROM DUAL
UNION ALL SELECT 'R1',7,'','B','BB','4171846','8000340',1 FROM DUAL
UNION ALL SELECT 'R1',8,'8000340','B','BB','4171846','8000340',1 FROM DUAL
UNION ALL SELECT 'R1',9,'8000340','C','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R1',10,'','C','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R1',11,'','C','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R1',12,'4120554','C','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R1',13,'4120554','D','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R1',14,'','D','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R1',15,'4171979','D','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R1',16,'4171979','E','EE','4171979','4120551',3 FROM DUAL
UNION ALL SELECT 'R1',17,'','E','EE','4171979','4120551',3 FROM DUAL
UNION ALL SELECT 'R1',18,'','E','EE','4171979','4120551',3 FROM DUAL
UNION ALL SELECT 'R1',19,'4120551','E','EE','4171979','4120551',3 FROM DUAL
UNION ALL SELECT 'R1',20,'4120551','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R1',21,'','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R1',22,'','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R1',23,'','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R1',24,'','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R1',25,'4198741','F','FF','4120551','8000340',3 FROM DUAL
UNION ALL SELECT 'R2',1,'8000340','G','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R2',2,'','G','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R2',3,'','G','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R2',4,'4120554','G','CC','8000340','4120554',2 FROM DUAL
UNION ALL SELECT 'R2',5,'4120554','H','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R2',6,'','H','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R2',7,'4171979','H','DD','4120554','4171979',2 FROM DUAL
UNION ALL SELECT 'R2',8,'4171979','I','GG','4171979','8005340',4 FROM DUAL
UNION ALL SELECT 'R2',9,'','I','GG','4171979','8005340',4 FROM DUAL
UNION ALL SELECT 'R2',10,'8005340','I','GG','4171979','8005340',4 FROM DUAL
)
SELECT * FROM FIN
위와 같이 5가지 항목을 구하고 싶은데
한번에 구하기는 어려울까요??
중간에 null이 있어서 어렵네요
sid는 구간에 대한 순번을 부여한것인데 r1에서 r2로 바뀌면 문제가 있네요 ㅠㅠ
ssid는 각 구간에 대해 유니크한 ID를 부여한것입니다. 중복되는 구간은 동일한 ID를 사용합니다.
dr_seq는 ssid가 유니크하게 부여된 순번입니다.
SID SSID는 숫자여도 상관없습니다
쿼리로는 힘들까요??
프로C로 해야하는지...
막막해서 올려봅니다.
부탁드려요~
|