데이터베이스 질문과 답변
SELECT '100' order_qty, 'genesis' item FROM dual;
WITH plan_tbl AS (
SELECT '범퍼' op,'50' qty, '20210901' dt ,'genesis' item FROM dual
UNION ALL
SELECT '범퍼' op,'50' qty, '20210902' dt ,'genesis' FROM dual
UNION ALL
SELECT '앞문' op,'100' qty, '20210903' dt ,'genesis' FROM dual
UNION ALL
SELECT '엔진' op,'30' qty, '20210905' dt ,'genesis' FROM dual
UNION ALL
SELECT '엔진' op,'70' qty, '20210906' dt ,'genesis' FROM dual
)
SELECT op,SUM(DECODE(dt,'20210901',qty,0))p20210901
, SUM(DECODE(dt,'20210902',qty,0))p20210902
, SUM(DECODE(dt,'20210903',qty,0))p20210903
, SUM(DECODE(dt,'20210905',qty,0))p20210905
, SUM(DECODE(dt,'20210906',qty,0))p20210906
FROM plan_tbl
group by op
오더테이블의 수량 100개를 조인하여 보여주기를 원합니다.
꾸벅
by 마농
[2021.10.05 10:43:32]
WITH order_tbl AS
(
SELECT 100 order_qty, 'genesis' item FROM dual
)
, plan_tbl AS
(
SELECT '범퍼' op, 50 qty, '20210901' dt, 'genesis' item FROM dual
UNION ALL SELECT '범퍼', 50, '20210902', 'genesis' FROM dual
UNION ALL SELECT '앞문', 100, '20210903', 'genesis' FROM dual
UNION ALL SELECT '엔진', 30, '20210905', 'genesis' FROM dual
UNION ALL SELECT '엔진', 70, '20210906', 'genesis' FROM dual
)
SELECT p.op
, o.order_qty
, SUM(DECODE(p.dt, '20210901', p.qty, 0)) p20210901
, SUM(DECODE(p.dt, '20210902', p.qty, 0)) p20210902
, SUM(DECODE(p.dt, '20210903', p.qty, 0)) p20210903
, SUM(DECODE(p.dt, '20210905', p.qty, 0)) p20210905
, SUM(DECODE(p.dt, '20210906', p.qty, 0)) p20210906
FROM plan_tbl p
, order_tbl o
WHERE p.item = o.item
GROUP BY p.op, o.order_qty
;
) 버튼을 클릭하여 작성 하시면 됩니다.