데이터베이스 질문과 답변
안녕하세요~ 장마입니다..장마에 쿼리 질문드려봅니다..ㅎㅎ
아래 테이블A와 임의 테이블을 조인시켜서 아래 원하는 결과값을 나오게 하고 싶은데
쉬운듯 안되네여..물론cd값은 거래처 코드로 여러가지 값이들어 있습니다.
A테이블과 1월~12월이 있는 가상 테이블을 outer조인하면 될꺼 같은데..
outer를 하면 달(mm)정보가 없는 row는 cd,yyyy 값이 없게나오게 되도라구여
테이블 A
--------------------------------------------------------------
cd yyyy mm plan_cnt
12334 2011 04 11
12334 2011 05 33
12334 2011 06 44
12334 2011 07 45
--------------------------------------------------------------
원하는 결과값
--------------------------------------------------------------
cd yyyy mm plan_cnt
12334 2011 01 11
12334 2011 02 33
12334 2011 03 44
12334 2011 04 45
12334 2011 05 11
12334 2011 06 33
12334 2011 07 44
12334 2011 08 45
12334 2011 09 11
12334 2011 10 33
12334 2011 11 44
12334 2011 12 45
--------------------------------------------------------------
제 생각으로 oueter걸면 아래와 같이 나오더라구여..ㅠㅠ
--------------------------------------------------------------
cd yyyy mm plan_cnt
01
02
03
12334 2011 04 45
12334 2011 05 11
12334 2011 06 33
12334 2011 07 44
08
09
10
11
12
--------------------------------------------------------------
select '12334' cd, '2011' yy, '04' mm, 11 cnt from dual union all
select '12334', '2011', '05', 33 from dual union all
select '12334', '2011', '06', 44 from dual union all
select '12334', '2011', '07', 45 from dual
)
select a.cd
, a.yy
, b.tmm
, a.cnt
from ( select cd
, yy
, row_number()over(order by mm) mm
, cnt
from t
) a
, ( select decode(mod(level, 4), 0, 4, mod(level, 4)) mm
, to_char(level, 'fm00') tmm
from dual
connect by level <= 12
) b
where a.mm = b.mm
order by b.tmm
답변주셔서 감사합니다.
근데 제가 질문을 좀 잘못올렸었습니다. ^.^:;;
제 질문의 요지는 거래처별 해당월에 실적이 없더라도 해당거래처 정보와 년월별 정보 및 실적0으로 출력되길 원합니다. ^.^
select '12334' cd, '2011' yy, '04' mm, 11 cnt from dual union all
select '12334', '2011', '05', 33 from dual union all
select '12334', '2011', '06', 44 from dual union all
select '12334', '2011', '07', 45 from dual
)
select *
FROM t
MODEL
PARTITION BY (cd,yy)
DIMENSION BY (mm)
MEASURES (cnt) IGNORE NAV
(
cnt[FOR mm IN (SELECT lpad(LEVEL,2,'0') FROM dual CONNECT BY LEVEL <= 12)] = cnt[cv()]
)
ORDER BY cd,yy,mm
with t as (
select '12334' cd, '2011' yy, '04' mm, 11 cnt from dual union all
select '12334', '2011', '05', 33 from dual union all
select '12334', '2011', '06', 44 from dual union all
select '12334', '2011', '07', 45 from dual
), v as (
select '12334' cd from dual
)
select b.cd
, max(a.yy) over() yy
, c.mm
, nvl(a.cnt, 0) cnt
from t a
, v b
, ( select to_char(level, 'fm00') mm
from dual
connect by level <= 12
) c
where c.mm = a.mm (+)
order by c.mm
select '12334' cd, '2011' yy, '04' mm, 11 cnt from dual union all
select '12334', '2011', '05', 33 from dual union all
select '12334', '2011', '06', 44 from dual union all
select '12334', '2011', '07', 45 from dual
)
SELECT m.cd, m.yy, m.mm, nvl(t.cnt,0) cnt
FROM (
SELECT *
FROM ( select DISTINCT cd, yy FROM t ) a
,( SELECT lpad(LEVEL,2,'0') mm FROM dual CONNECT BY LEVEL <= 12) c
) m
, t
WHERE m.cd = t.cd(+)
AND m.yy = t.yy(+)
AND m.mm = t.mm(+)
ORDER BY 1,2,3
(
SELECT '12334' cd, '2011' yyyy, '04' mm, 11 plan_cnt FROM dual
UNION ALL SELECT '12334', '2011', '05', 33 FROM dual
UNION ALL SELECT '12334', '2011', '06', 44 FROM dual
UNION ALL SELECT '12334', '2011', '07', 45 FROM dual
)
SELECT a.cd
, a.yyyy
, b.mm
, c.plan_cnt
FROM (SELECT DISTINCT cd, yyyy FROM t) a
CROSS JOIN (SELECT LPAD(level, 2, '0') mm FROM dual CONNECT BY level <= 12) b
LEFT OUTER JOIN t c
ON a.cd = c.cd
AND a.yyyy = c.yyyy
AND b.mm = c.mm
ORDER BY a.cd, a.yyyy, b.mm
;
) 버튼을 클릭하여 작성 하시면 됩니다.