데이터베이스 질문과 답변
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]
마농님 감사합니다~ (__) 복많이 받으셔요
) 버튼을 클릭하여 작성 하시면 됩니다.