데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

쿼리가 힘드네여..ㅠ

작성자

박진호(zooaak)

작성일

2012-06-26 17:01ⓒ
2012-06-27 09:23ⓜ

조회수

5,792

WITH test AS
(
SELECT   '201206' as yyyyMM ,1 as dd ,100 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,2 as dd ,100 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,3 as dd ,200 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,4 as dd ,200 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,5 as dd ,200 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,6 as dd ,300 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,7 as dd ,300 as d2 FROM dual union ALL
SELECT   '201206' as yyyyMM ,9 as dd ,300 as d2 FROM dual
)
,test_d2 as
(
select distinct
d2
from test
where yyyymm = '201206'
)
SELECT
'201206' YYYYMM
,D1.X DD
,D2.D2 D2
FROM
           (SELECT       LEVEL X
            FROM         DUAL
            CONNECT BY   LEVEL <= TO_CHAR (LAST_DAY (TO_DATE ('201206', 'yyyyMM')), 'DD')) D1
            CROSS JOIN TEST_D2 D2
LEFT OUTER JOIN TEST A
         ON (
         A.dd = D1.X
         AND A.D2 = D2.D2
         )

혹시 이걸 원하시는지요...

왕초보님이 2012-06-26 19:45에 작성한 댓글입니다. Edit Ｘ

WITH test AS
(
SELECT '201206' yyyymm, 1 dd, 100 d2 FROM dual
UNION ALL SELECT '201206', 2, 100 FROM dual
UNION ALL SELECT '201206', 3, 200 FROM dual
UNION ALL SELECT '201206', 4, 200 FROM dual
UNION ALL SELECT '201206', 5, 200 FROM dual
UNION ALL SELECT '201206', 6, 300 FROM dual
UNION ALL SELECT '201206', 7, 300 FROM dual
UNION ALL SELECT '201206', 9, 300 FROM dual
)
SELECT *
FROM (SELECT LEVEL X
          FROM dual
         CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE('201206', 'yyyymm')), 'dd')
        ) d
LEFT OUTER JOIN test a
PARTITION BY (a.d2)
    ON a.dd = d.x
;