지금 데이터는
A | B | C
--------------------------
20170101 | 1000
20170101 | 1100
20170101 | 1200
20170101 | 1300
이렇게 있습니다.. 제가 뿌려주고 싶은거는요
A | B | C
--------------------------------
20170101 | 0900 | N
20170101 | 1000 | Y
20170101 | 1100 | Y
20170101 | 1200 | Y
20170101 | 1300 | Y
20170101 | 1400 | N
20170101 | 1500 | N
20170101 | 1600 | N
20170101 | 1700 | N
20170101 | 1800 | N
이렇게 뿌려주고 싶은데요.
초보적인 질문 죄송합니다 ㅠㅜ
WITH t AS ( SELECT '20170101' dt, '1000' tm FROM dual UNION ALL SELECT '20170101', '1100' FROM dual UNION ALL SELECT '20170101', '1200' FROM dual UNION ALL SELECT '20170101', '1300' FROM dual ) SELECT a.dt , a.tm , NVL2(b.tm, 'Y', 'N') yn FROM (SELECT '20170101' dt , LPAD((LEVEL + 8), 2, '0') || '00' tm FROM dual CONNECT BY LEVEL <= 10 ) a LEFT OUTER JOIN t b ON a.dt = b.dt AND a.tm = b.tm ORDER BY tm ;
SELECT a.dt , b.tm , CASE WHEN c.tm IS NULL THEN 'N' ELSE 'Y' END yn FROM (SELECT '20170101' dt) a CROSS JOIN (SELECT '0900' tm UNION ALL SELECT '1000' UNION ALL SELECT '1100' UNION ALL SELECT '1200' UNION ALL SELECT '1300' UNION ALL SELECT '1400' UNION ALL SELECT '1500' UNION ALL SELECT '1600' UNION ALL SELECT '1700' UNION ALL SELECT '1800' ) b LEFT OUTER JOIN (-- data_table -- SELECT '20170101' dt, '1000' tm UNION ALL SELECT '20170101', '1100' UNION ALL SELECT '20170101', '1200' UNION ALL SELECT '20170101', '1300' ) c ON a.dt = c.dt AND b.tm = c.tm ORDER BY dt, tm ;