다:1 조인 문의 건 0 1 203

by 정도경영 [Oracle 기초] sql join [2021.10.04 12:37:24]


n대1조인.png (56,703Bytes)

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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입