데이터베이스 질문과 답변
안녕하세요, 선배님들.
이전에 비슷한 내용으로 질문을 올렸던 적이 있는데요,
현재 쿼리는 다음과 같습니다.
SELECT A.COS_ORD_CD, A.COS_QTY, A.COS_IRS_GB, A.COS_TAX_FG, A.COS_PAY, A.COS_ORD_LS_SQ, A.COS_APPRO_FG, A.COS_STRD_GB, A.COS_UNIT, IF(A.COS_IRS_GB='품목', (SELECT COS_CLS01_NM FROM COS_CLS02 B2 INNER JOIN COS_CLS01 B3 ON B3.COS_CLS01_CD = B2.COS_CLS01_CD WHERE B2.COS_CLS02_CD = B1.COS_CLS02_CD), IF(A.COS_IRS_GB='원료', (SELECT COS_CLS01_NM FROM COS_CLS02 C2 INNER JOIN COS_CLS01 C3 ON C3.COS_CLS01_CD = C2.COS_CLS01_CD WHERE C2.COS_CLS02_CD = C1.COS_CLS02_CD), IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS01_NM FROM COS_CLS02 D2 INNER JOIN COS_CLS01 D3 ON D3.COS_CLS01_CD = D2.COS_CLS01_CD WHERE D2.COS_CLS02_CD = D1.COS_CLS02_CD), '' ))) AS COS_CLS01_NM, IF(A.COS_IRS_GB='품목', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI B2 INNER JOIN COS_CLS02 B3 ON B3.COS_CLS02_BI_CD = B2.COS_CLS02_BI_CD WHERE B3.COS_CLS02_CD = B1.COS_CLS02_CD), IF(A.COS_IRS_GB='원료', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI C2 INNER JOIN COS_CLS02 C3 ON C3.COS_CLS02_BI_CD = C2.COS_CLS02_BI_CD WHERE C3.COS_CLS02_CD = C1.COS_CLS02_CD), IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI D2 INNER JOIN COS_CLS02 D3 ON D3.COS_CLS02_BI_CD = D2.COS_CLS02_BI_CD WHERE D3.COS_CLS02_CD = D1.COS_CLS02_CD), '' ))) AS COS_CLS02_BI_NM, IF(A.COS_IRS_GB='품목', (SELECT SUM(COS_QTY) FROM COS_ORD_LS B2 WHERE B2.COS_ITEM_CD = B1.COS_ITEM_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != B2.COS_ORD_LS_SQ), IF(A.COS_IRS_GB='원료', (SELECT SUM(COS_QTY) FROM COS_ORD_LS C2 WHERE C2.COS_RAW_MTRL_CD = C1.COS_RAW_MTRL_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != C2.COS_ORD_LS_SQ), IF(A.COS_IRS_GB='부자재', (SELECT SUM(COS_QTY) FROM COS_ORD_LS D2 WHERE D2.COS_SUB_RC_CD = D1.COS_SUB_RC_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != D2.COS_ORD_LS_SQ), '' ))) AS COS_OO_QTY , (SELECT SUM(COS_QTY) FROM COS_WARE_LS WHERE COS_ORD_LS_SQ = A.COS_ORD_LS_SQ) AS cos_now_qty, C03.COS_CLS03_NM AS CLS_NAME -- LEFT OUTER JOIN으로 처리한 코드명 FROM COS_ORD_LS A LEFT OUTER JOIN COS_ITEM B1 ON B1.COS_ITEM_CD = A.COS_ITEM_CD LEFT OUTER JOIN COS_RAW_MTRL C1 ON C1.COS_RAW_MTRL_CD = A.COS_RAW_MTRL_CD LEFT OUTER JOIN COS_SUB_RC D1 ON D1.COS_SUB_RC_CD = A.COS_SUB_RC_CD LEFT OUTER JOIN COS_CLS03 C03 ON C03.COS_CLS03_CD = (CASE A.COS_IRS_GB WHEN '품목' THEN B1.COS_CLS03_CD WHEN '원료' THEN C1.COS_CLS03_CD WHEN '부자재' THEN D1.COS_CLS03_CD ELSE '' END) WHERE A.COS_ORD_CD='ORD20201210111051' ORDER BY A.COS_ORD_LS_SQ DESC;
SELECT 절 마지막에 포함된 컬럼인 "CLS_NAME"은 이전에 마농(선생)님께서 가르쳐 주신 대로 변경을 하였는데요,
"COS_CLS01_NM", "COS_CLS02_BI_NM"과 같이 JOIN-WHERE가 포함된 서브쿼리나
"COS_OO_QTY", "COS_NOW_QTY"와 같이 집계 함수가 포함된 서브쿼리도 축약이 가능할까요?..
만약 가능하다면, 어떠한 형태로 처리할 수 있는지 궁금합니다..
인라인 서브쿼리와 제가 아는 여러 가지 조인을 이용해서 시도해 보았지만 원하는 결과가 나오지 않는 상황입니다...
선배님들의 소중한 조언 감사히 듣도록 하겠습니다.
답변 미리 감사드립니다. ^^
(현재 셀렉트되는 결과는 첨부파일에 추가하였습니다!)
by 마농
[2021.01.20 13:25:08]
cos_cls01_nm, cos_cls02_bi_nm 과 같이 join-where 가 포함된 서브쿼리는 쉽게 조인형태로 변경이 가능하지만
cos_oo_qty, cos_now_qty 와 같이 집계 함수가 포함된 서브쿼리는 축약이 까다롭습니다.
조인 후 집계 해야 할지? 집계후 조인해야 할지? 성능도 고려해야 하므로 생각할 게 많습니다.
cos_now_qty 의 경우엔 그래도 조건이 명확해 보이는데.
cos_oo_qty 의 경우엔 부정 조건도 사용되고 테이블도 메인과 동일한 테이블이네요?
이 항목이 어떤 의미로 사용된 것인지 불확실 하네요.
결과 이미지를 봐도 값이 NULL 인 듯 하고요.
조건이 정확하게 주어진 것이 맞는지?
동일 테이블인데? 메인 조건인 (cos_ord_cd = 'ORD20201210111051') 조건이 서브에는 없고
정확한 변환을 위해서는 각 테이블의 PK 와 서로간의 관계.
각 항목들의 의미 등을 알아야 합니다.
by 찡스쿄
[2021.01.20 14:21:01]
마농 선생님 답변 감사드립니다!
말씀하신 JOIN-WHERE를 이렇게 쿼리를 작성해 보았는데.. 결과는 올바르게 나오고 있습니다..
COS_CLS01_NM을 처리하는 기존의(주석 처리된) 스칼라 서브 쿼리가 올바른 형태로 변경된 게 맞을까요?..
SELECT A.COS_ORD_CD, A.COS_QTY, A.COS_IRS_GB, A.COS_TAX_FG, A.COS_PAY, A.COS_ORD_LS_SQ, A.COS_APPRO_FG, A.COS_STRD_GB, A.COS_UNIT, /*IF(A.COS_IRS_GB='품목', (SELECT COS_CLS01_NM FROM COS_CLS02 B2 INNER JOIN COS_CLS01 B3 ON B3.COS_CLS01_CD = B2.COS_CLS01_CD WHERE B2.COS_CLS02_CD = B1.COS_CLS02_CD), IF(A.COS_IRS_GB='원료', (SELECT COS_CLS01_NM FROM COS_CLS02 C2 INNER JOIN COS_CLS01 C3 ON C3.COS_CLS01_CD = C2.COS_CLS01_CD WHERE C2.COS_CLS02_CD = C1.COS_CLS02_CD), IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS01_NM FROM COS_CLS02 D2 INNER JOIN COS_CLS01 D3 ON D3.COS_CLS01_CD = D2.COS_CLS01_CD WHERE D2.COS_CLS02_CD = D1.COS_CLS02_CD), '' ))) AS COS_CLS01_NM,*/ CD.COS_CLS01_NM, -- 바로 앞 라인에 주석처리한 COS_CLS01_NM을 FROM 절에서 JOIN으로 변경하여 조회(CASE 문 이용) IF(A.COS_IRS_GB='품목', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI B2 INNER JOIN COS_CLS02 B3 ON B3.COS_CLS02_BI_CD = B2.COS_CLS02_BI_CD WHERE B3.COS_CLS02_CD = B1.COS_CLS02_CD), IF(A.COS_IRS_GB='원료', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI C2 INNER JOIN COS_CLS02 C3 ON C3.COS_CLS02_BI_CD = C2.COS_CLS02_BI_CD WHERE C3.COS_CLS02_CD = C1.COS_CLS02_CD), IF(A.COS_IRS_GB='부자재', (SELECT COS_CLS02_BI_NM FROM COS_CLS02_BI D2 INNER JOIN COS_CLS02 D3 ON D3.COS_CLS02_BI_CD = D2.COS_CLS02_BI_CD WHERE D3.COS_CLS02_CD = D1.COS_CLS02_CD), '' ))) AS COS_CLS02_BI_NM, IF(A.COS_IRS_GB='품목', (SELECT SUM(COS_QTY) FROM COS_ORD_LS B2 WHERE B2.COS_ITEM_CD = B1.COS_ITEM_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != B2.COS_ORD_LS_SQ), IF(A.COS_IRS_GB='원료', (SELECT SUM(COS_QTY) FROM COS_ORD_LS C2 WHERE C2.COS_RAW_MTRL_CD = C1.COS_RAW_MTRL_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != C2.COS_ORD_LS_SQ), IF(A.COS_IRS_GB='부자재', (SELECT SUM(COS_QTY) FROM COS_ORD_LS D2 WHERE D2.COS_SUB_RC_CD = D1.COS_SUB_RC_CD AND COS_STRD_GB = 'N' AND A.COS_ORD_LS_SQ != D2.COS_ORD_LS_SQ), '' ))) AS COS_OO_QTY , (SELECT SUM(COS_QTY) FROM COS_WARE_LS WHERE COS_ORD_LS_SQ = A.COS_ORD_LS_SQ) AS cos_now_qty, C03.COS_CLS03_NM AS CLS_NAME -- LEFT OUTER JOIN으로 처리한 코드명 FROM COS_ORD_LS A LEFT OUTER JOIN COS_ITEM B1 ON B1.COS_ITEM_CD = A.COS_ITEM_CD LEFT OUTER JOIN COS_RAW_MTRL C1 ON C1.COS_RAW_MTRL_CD = A.COS_RAW_MTRL_CD LEFT OUTER JOIN COS_SUB_RC D1 ON D1.COS_SUB_RC_CD = A.COS_SUB_RC_CD LEFT OUTER JOIN COS_CLS03 C03 ON C03.COS_CLS03_CD = (CASE A.COS_IRS_GB WHEN '품목' THEN B1.COS_CLS03_CD WHEN '원료' THEN C1.COS_CLS03_CD WHEN '부자재' THEN D1.COS_CLS03_CD ELSE '' END) /* COS_CLS01_NM 스칼라 서브쿼리를 다음과 같은 형태의 조인으로 변경 */ LEFT OUTER JOIN ( SELECT C01.COS_CLS01_NM, C02.COS_CLS02_CD FROM COS_CLS02 AS C02 INNER JOIN COS_CLS01 C01 ON C01.COS_CLS01_CD = C02.COS_CLS01_CD ) AS CD ON CD.COS_CLS02_CD = (CASE A.COS_IRS_GB WHEN '품목' THEN B1.COS_CLS02_CD WHEN '원료' THEN C1.COS_CLS02_CD WHEN '부자재' THEN D1.COS_CLS02_CD ELSE '' END) /* =================================================== */ WHERE A.COS_ORD_CD='ORD20201210111051' ORDER BY A.COS_ORD_LS_SQ DESC
by 마농
[2021.01.20 14:41:01]
SELECT a.cos_ord_cd
, a.cos_qty
, a.cos_irs_gb
, a.cos_tax_fg
, a.cos_pay
, a.cos_ord_ls_sq
, a.cos_appro_fg
, a.cos_strd_gb
, a.cos_unit
, c01.cos_cls01_nm
, c2b.cos_cls02_bi_nm
, (SELECT SUM(b.cos_qty)
FROM cos_ord_ls b
WHERE b.cos_strd_gb = 'N'
AND b.cos_ord_ls_sq != a.cos_ord_ls_sq
AND CASE WHEN a.cos_irs_gb = '품목' AND b.cos_item_cd = b1.cos_item_cd THEN 1
WHEN a.cos_irs_gb = '원료' AND b.cos_raw_mtrl_cd = c1.cos_raw_mtrl_cd THEN 1
WHEN a.cos_irs_gb = '부자재' AND b.cos_sub_rc_cd = d1.cos_sub_rc_cd THEN 1
END = 1
) cos_oo_qty
, (SELECT SUM(cos_qty) FROM cos_ware_ls WHERE cos_ord_ls_sq = a.cos_ord_ls_sq) cos_now_qty
, c03.cos_cls03_nm AS cls_name
FROM cos_ord_ls a
LEFT JOIN cos_item b1 ON a.cos_item_cd = b1.cos_item_cd
LEFT JOIN cos_raw_mtrl c1 ON a.cos_raw_mtrl_cd = c1.cos_raw_mtrl_cd
LEFT JOIN cos_sub_rc d1 ON a.cos_sub_rc_cd = d1.cos_sub_rc_cd
LEFT JOIN cos_cls03 c03 ON c03.cos_cls03_cd = (CASE a.cos_irs_gb
WHEN '품목' THEN b1.cos_cls03_cd
WHEN '원료' THEN c1.cos_cls03_cd
WHEN '부자재' THEN d1.cos_cls03_cd
ELSE '' END)
LEFT JOIN cos_cls02 c02 ON c02.cos_cls02_cd = (CASE a.cos_irs_gb
WHEN '품목' THEN b1.cos_cls02_cd
WHEN '원료' THEN c1.cos_cls02_cd
WHEN '부자재' THEN d1.cos_cls02_cd
ELSE '' END)
LEFT JOIN cos_cls01 c01 ON c02.cos_cls01_cd = c01.cos_cls01_cd
LEFT JOIN cos_cls02_bi c2b ON c02.cos_cls01_cd = c2b.cos_cls02_bi_cd
WHERE a.cos_ord_cd = 'ORD20201210111051'
ORDER BY a.cos_ord_ls_sq DESC
;
by 찡스쿄
[2021.01.20 15:02:40]
마농 선생님, 답변 감사드립니다. ^^
앞에서 말씀하신 의미가 명확하지 않은 컬럼은 우선은 제외해야 할 듯합니다 ^^..
오늘도 도움 주셔서 정말 감사드립니다.
선생님 덕분에 하루 하루 쿼리 실력이 늘고있는 것을 느끼게 됩니다. (- 꾸벅 -)
매번 정말 진심으로 감사드립니다.
좋은 한 주 보내시길 바라요. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.