안녕하십니까ㅎㅎ 좋은 아침입니다!! 제가 특정데이터를 아래 결과데이터 처럼 보여줘야하는데 일단 지금 어찌됬건 값은 나왔습니다..근데 이렇게 해야하나..싶네요..ㅠㅠ 구루비 분들이라면 더 간단하고 효율적인 방법을 찾으시지 않을까 싶어서 질문드립니다! 이럴경우 그룹을 어떻게 쳐야 하나요? WITH T AS ( SELECT '01' SEQ, 'EN' N_DATA , '01' AS FRNUM, '02' AS TONUM FROM DUAL UNION ALL SELECT '02' , 'EN' , '03' , '04' FROM DUAL UNION ALL SELECT '03' , 'RN' , '05' , '06' FROM DUAL UNION ALL SELECT '04' , 'RN' , '07' , '08' FROM DUAL UNION ALL SELECT '05' , '' , '09' , '10' FROM DUAL UNION ALL SELECT '06' , 'TN' , '11' , '12' FROM DUAL UNION ALL SELECT '07' , 'EN' , '13' , '14' FROM DUAL UNION ALL SELECT '08' , 'EN' , '15' , '16' FROM DUAL UNION ALL SELECT '09' , 'RN' , '17' , '18' FROM DUAL UNION ALL SELECT '10' , 'RN' , '19' , '20' FROM DUAL) SELECT * FROM T ; /* 희망결과값 N_DATA FRNUM TONUM --- ---- ---- EN 01 04 --- ---- ---- RN 05 08 --- ---- ---- TN 11 12 --- ---- ---- EN 13 16 --- ---- ---- RN 17 20 */
-- 수정했습니다. WITH T AS ( SELECT '01' SEQ, 'EN' N_DATA , '01' AS FRNUM, '02' AS TONUM FROM DUAL UNION ALL SELECT '02' , 'EN' , '03' , '04' FROM DUAL UNION ALL SELECT '03' , 'RN' , '05' , '06' FROM DUAL UNION ALL SELECT '04' , 'RN' , '07' , '08' FROM DUAL UNION ALL SELECT '05' , '' , '09' , '10' FROM DUAL UNION ALL SELECT '06' , 'TN' , '11' , '12' FROM DUAL UNION ALL SELECT '07' , 'EN' , '13' , '14' FROM DUAL UNION ALL SELECT '08' , 'EN' , '15' , '16' FROM DUAL UNION ALL SELECT '09' , 'RN' , '17' , '18' FROM DUAL UNION ALL SELECT '10' , 'RN' , '19' , '20' FROM DUAL) SELECT N_DATA , MIN(FRNUM) , MAX(TONUM) FROM (SELECT A.* , ROW_NUMBER() OVER(PARTITION BY N_DATA ORDER BY SEQ) -TO_NUMBER(SEQ) GB FROM T A WHERE N_DATA IS NOT NULL ) GROUP BY N_DATA , GB ORDER BY MAX(TO_NUMBER(SEQ))
SELECT n_data,MIN(frnum),MAX(tonum) FROM( SELECT n_data ,frnum ,tonum ,flag ,SUM(flag) OVER(ORDER BY frnum) flag2 FROM( SELECT n_data ,frnum ,tonum ,CASE WHEN n_data =LEAD(n_data) OVER(ORDER BY frnum) THEN 1 WHEN LAG(n_data) OVER(ORDER BY frnum) IS NULL THEN 1 ELSE 0 END flag FROM t ) WHERE n_data IS NOT NULL ) GROUP BY n_data,flag2 ORDER BY flag2