데이터베이스 질문과 답변
WITH TT AS (
SELECT '2014','반품','4810' FROM DUAL
UNION ALL
SELECT '2014','배분','1231' FROM DUAL
UNION ALL
SELECT '2015','반품','7741' FROM DUAL
UNION ALL
SELECT '2015','배분','9844' FROM DUAL
UNION ALL
SELECT '2015','주문','410' FROM DUAL
UNION ALL
SELECT '2016','반품','1234' FROM DUAL
UNION ALL
SELECT '2016','배분','1234' FROM DUAL
UNION ALL
SELECT '2016','주문','7785' FROM DUAL
UNION ALL
SELECT '2017','반품','2310' FROM DUAL
UNION ALL
SELECT '2017','배분','7791' FROM DUAL
UNION ALL
SELECT '2017','주문','0014' FROM DUAL
)SELECT * FROM TT
2014 반품 4810
2014 배분 1231
2015 반품 7741
2015 배분 9844
2015 주문 410
2016 반품 1234
2016 배분 1234
2016 주문 7785
2017 반품 2310
2017 배분 7791
2017 주문 0014
2015,2016,2017 모두 '배분','주문','반품' 이 존재하는데 2014 년도에는 '주문' 항목이 없습니다.
2014년도에는 주문자체가 없어서 , row 에 나오질않았는데.
group by 해도 데이터가 존재하지않을때 그 누락된 row 를 조회할수 있는 방법이 어떤것들이 있을까요?
주문항목이 없으니 2014 , '주문' , '0' 으로 나오게끔 하고싶습니다
원하는 결과값
2014 반품 4810
2014 배분 1231
2014 주문 0
2015 반품 7741
2015 배분 9844
2015 주문 410
2016 반품 1234
2016 배분 1234
2016 주문 7785
2017 반품 2310
2017 배분 7791
2017 주문 0014
with tt as ( select '2014' col1,'반품' col2,'4810' col3 from dual union all select '2014','배분','1231' from dual union all select '2015','반품','7741' from dual union all select '2015','배분','9844' from dual union all select '2015','주문','410' from dual union all select '2016','반품','1234' from dual union all select '2016','배분','1234' from dual union all select '2016','주문','7785' from dual union all select '2017','반품','2310' from dual union all select '2017','배분','7791' from dual union all select '2017','주문','0014' from dual ), t as ( select '반품' col from dual union all select '배분'from dual union all select '주문'from dual ) select col1,col,nvl(col3,0) col3 from t left outer join tt partition by (tt.col1) on (t.col = tt.col2)
SELECT *
FROM t
MODEL
PARTITION BY (연도)
DIMENSION BY (구분)
MEASURES (값)
IGNORE NAV
RULES (값[FOR 구분 IN ('반품','배분','주문')] = 값[CV()])
ORDER BY 연도, 구분
;
) 버튼을 클릭하여 작성 하시면 됩니다.