마농님 몇일 전 쿼리 관련해서 조금 더 여쭤봐도 될까요? 0 4 167

by woni_rang [SQL Query] [2018.01.05 13:20:21]


WITH data_t(emp_num, doc_no, cal, op1, op2, op3, create_dt) AS
(
SELECT 111111, 13000, 10,  1,  2,  3, '2017-11-31' FROM dual
UNION ALL SELECT 111111, 14000, 10,  7,  8,  6, '2017-12-31' FROM dual
UNION ALL SELECT 222222, 14001, 10,  8,  8,  7, '2018-01-02' FROM dual
UNION ALL SELECT 111111, 14000, 50, 49, 50, 51, '2017-12-31' FROM dual
UNION ALL SELECT 111111, 13000, 30,  3,  4,  5, '2017-11-31' FROM dual
)
, code_t AS
(
SELECT 10 cal FROM dual
UNION ALL SELECT  30 FROM dual
UNION ALL SELECT  50 FROM dual
UNION ALL SELECT  70 FROM dual
UNION ALL SELECT 100 FROM dual
)
SELECT a.cal
     , b.gb
     , b.op1, b.op2, b.op3
  FROM code_t a
  LEFT OUTER JOIN
       (SELECT *
          FROM (SELECT cal
                     , RANK() OVER(ORDER BY create_dt) gb  
                     , op1, op2, op3
                  FROM data_t
                 WHERE emp_num = 111111
                )
         WHERE gb = 1
         UNION ALL
        SELECT cal
             , 2 gb
             , op1, op2, op3
          FROM data_t
         WHERE doc_no = 14000
        ) b
 PARTITION BY (b.gb)
   ON a.cal = b.cal
 ORDER BY a.cal, b.gb
;

개발 요건이 바뀌어서 알려주신 쿼리를 수정하여 진행중인데요.
한가지 더 left outer join한 테이블에 and 조건으로 조회를 하는데
조건에 해당하는 데이터가 없으면 한건도 안 나오게 되는데
조건에 해당하는 데이터가 없더라도 code_t a 테이블에 있는
기준값과 gb는 나오게 할수는 없을까요?

예를 들어 b테이블에 조건에 해당하는 데이터가 없더라도 아래 포맷으로 조회가 되게끔 가능할까요?
계속 귀찮게해서 죄송합니다.(__)

 

기준 구분 op1 op2 op3
10      
10      
30      
30      
50      
50      
70      
70      
100      
100      

 

by 삐르짱 [2018.01.05 17:39:33]
WITH DATA_T(EMP_NUM, DOC_NO, CAL, OP1, OP2, OP3, CREATE_DT) AS
(
  SELECT 111111, 13000, 10,  1,  2,  3, '2017-11-31' FROM DUAL UNION ALL
  SELECT 111111, 14000, 10,  7,  8,  6, '2017-12-31' FROM DUAL UNION ALL
  SELECT 222222, 14001, 10,  8,  8,  7, '2018-01-02' FROM DUAL UNION ALL
  SELECT 111111, 14000, 50, 49, 50, 51, '2017-12-31' FROM DUAL UNION ALL
  SELECT 111111, 13000, 30,  3,  4,  5, '2017-11-31' FROM DUAL
), 
CODE_T(CAL, RN) AS
(
  SELECT 10 , 1  FROM DUAL UNION ALL
  SELECT 10 , 2  FROM DUAL UNION ALL
  SELECT 30 , 1  FROM DUAL UNION ALL
  SELECT 30 , 2  FROM DUAL UNION ALL
  SELECT 50 , 1  FROM DUAL UNION ALL
  SELECT 50 , 2  FROM DUAL UNION ALL
  SELECT 70 , 1  FROM DUAL UNION ALL
  SELECT 70 , 2  FROM DUAL UNION ALL
  SELECT 100, 1  FROM DUAL UNION ALL
  SELECT 100, 2  FROM DUAL
)
SELECT A.*
     , B.EMP_NUM
     , B.DOC_NO
     , B.CAL
     , B.OP1
     , B.OP2
     , B.OP3
     , B.CREATE_DT
  FROM CODE_T A
     , (SELECT EMP_NUM
             , DOC_NO
             , CAL
             , OP1
             , OP2
             , OP3
             , CREATE_DT
             , RN
          FROM (SELECT EMP_NUM
                     , DOC_NO
                     , CAL
                     , OP1
                     , OP2
                     , OP3
                     , CREATE_DT
                     , RANK() OVER(ORDER BY CREATE_DT) AS RN
                  FROM DATA_T
                 WHERE EMP_NUM = 111112
               )
         WHERE RN = 1
         UNION ALL 
        SELECT EMP_NUM
             , DOC_NO
             , CAL
             , OP1
             , OP2
             , OP3
             , CREATE_DT
             , 2 AS RN
          FROM DATA_T
         WHERE EMP_NUM = 111112
           AND DOC_NO  = 14000
       ) B
 WHERE A.CAL = B.CAL(+) 
   AND A.RN  = B.RN (+)
 ORDER BY A.CAL
        , A.RN

 


by woni_rang [2018.01.08 10:05:02]

삐르짱님도 감사드려요(__) 복받으세요


by 마농 [2018.01.08 08:54:58]
WITH data_t(emp_num, doc_no, cal, op1, op2, op3, create_dt) AS
(
SELECT 111111, 13000, 10,  1,  2,  3, '2017-11-31' FROM dual
UNION ALL SELECT 111111, 14000, 10,  7,  8,  6, '2017-12-31' FROM dual
UNION ALL SELECT 222222, 14001, 10,  8,  8,  7, '2018-01-02' FROM dual
UNION ALL SELECT 111111, 14000, 50, 49, 50, 51, '2017-12-31' FROM dual
UNION ALL SELECT 111111, 13000, 30,  3,  4,  5, '2017-11-31' FROM dual
)
, code_t AS
(
SELECT 10 cal FROM dual
UNION ALL SELECT  30 FROM dual
UNION ALL SELECT  50 FROM dual
UNION ALL SELECT  70 FROM dual
UNION ALL SELECT 100 FROM dual
)
SELECT a.cal
     , c.gb
     , b.op1, b.op2, b.op3
  FROM code_t a
 CROSS JOIN
       (SELECT LEVEL gb FROM dual CONNECT BY LEVEL <= 2) c
  LEFT OUTER JOIN
       (SELECT *
          FROM (SELECT cal
                     , RANK() OVER(ORDER BY create_dt) gb  
                     , op1, op2, op3
                  FROM data_t
                 WHERE emp_num = 1111119
                )
         WHERE gb = 1
         UNION ALL
        SELECT cal
             , 2 gb
             , op1, op2, op3
          FROM data_t
         WHERE doc_no = 140009
        ) b
   ON a.cal = b.cal
  AND c.gb  = b.gb
 ORDER BY a.cal, c.gb
;

 


by woni_rang [2018.01.08 10:05:21]

마농님 감사합니다~ (__) 복많이 받으셔요

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