SID OBUNO SRDATE SRTIME CSID CARNO SRTYPE
32507058 00010110ZZZ25041 20150302 002529 P4354ZZZ0453 79??619 21
32507063 00010110ZZZ25041 20150302 003839 P4785ZZZ1977 79??619 80
201129113 00010110ZZZ25041 20150216 161027 P4354ZZZ0453 79??619 30
이런 데이터가 있습니다.
다름이 아니라 lead(), lag() 함수를 써서 SRTYPE='80' 기준으로 앞뒤 ROW를 구하고 싶습니다.
SELECT LAG(SID) OVER (ORDER BY SID)앞수,
SID 해당수,
LEAD(SID) OVER(ORDER BY SID)뒷수 FROM
(
SELECT SID FROM OBUMANAGER
WHERE OBUNO = '0001011096925041'
AND SRTYPE = '80'
)
ORDER BY SID
;
select SID,
SID - lead(SID,1) over(order by SID) next,
SID - lag(SID,1) over(order by SID) prev from OBUMANAGER
WHERE SRTYPE = '80'
AND OBUNO = '00010110ZZZ5041'
;
WITH BASE AS ( select '32507058' AS SID , '00010110ZZZ25041' AS OBUNO , '20150302' AS SRDATE , '002529' AS SRTIME , 'P4354ZZZ0453' AS CSID , '79수619' AS CARNO ,'21' AS SRTYPE from dual union all select '32507063' AS SID , '00010110ZZZ25041' AS OBUNO , '20150302' AS SRDATE , '003839' AS SRTIME , 'P4785ZZZ1977' AS CSID , '79수619' AS CARNO ,'80' AS SRTYPE from dual union all select '201129113' AS SID , '00010110ZZZ25041' AS OBUNO , '20150216' AS SRDATE , '161027' AS SRTIME , 'P4354ZZZ0453' AS CSID , '79수619' AS CARNO ,'30' AS SRTYPE from dual ) select SID , OBUNO , SRDATE , SRTIME , CSID , CARNO , LAG(SRTYPE,1) OVER(ORDER BY SRTYPE) AS 앞수 , LEAD(SRTYPE,1) OVER (ORDER BY SRTYPE) AS 뒷수 , SRTYPE FROM BASE ORDER BY SRTYPE
이런 결과를 원하시는건지..
SELECT * FROM ( SELECT LEAD ( srtype ) OVER ( ORDER BY sid ) lead_rn , LAG ( srtype ) OVER ( ORDER BY sid ) lag_rn , t.* FROM t ) WHERE lead_rn = 80 OR lag_rn = 80 OR srtype = 80
--
SELECT *
FROM (
SELECT DECODE ( srtype
, '80'
, '80'
, DECODE ( LEAD ( srtype ) OVER ( ORDER BY sid )
, '80'
, '80'
, LAG ( srtype ) OVER ( ORDER BY sid )
)
) srtype
, sid, obuno, srdate, srtime, csid, carno
FROM t
) WHERE srtype = 80
WITH OBUMANAGER(SID, OBUNO, SRDATE, SRTIME, CSID, CARNO, SRTYPE) AS( SELECT 32507078, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '11' FROM DUAL UNION ALL SELECT 32507048, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '41' FROM DUAL UNION ALL SELECT 32507058, '00010110ZZZ25041', '20150302', '002529', 'P4354ZZZ0453', '79??619', '21' FROM DUAL UNION ALL SELECT 32507063, '00010110ZZZ25041', '20150302', '003839', 'P4785ZZZ1977', '79??619', '80' FROM DUAL UNION ALL SELECT 201129113, '00010110ZZZ25041', '20150216', '161027', 'P4354ZZZ0453', '79??619', '30' FROM DUAL ) , T1 AS ( SELECT ROW_NUMBER() OVER(ORDER BY SID) RN , OBUMANAGER.* FROM OBUMANAGER WHERE OBUNO = '00010110ZZZ25041' ) SELECT * FROM T1 WHERE RN IN ( SELECT T1.RN + ADD_RN - 2 FROM T1 , (SELECT ROWNUM ADD_RN FROM DUAL CONNECT BY LEVEL <= 3) WHERE SRTYPE= '80' ) ORDER BY SID ;