데이터베이스 질문과 답변
오라클 FULL OUTER JOIN -> MariaDB에서 표현을 어떻게하나요..??
0
3
4,114
by 꼬막 [SQL Query] Oracle MariaDB [2021.08.25 10:11:08]
오라클에서 마리아디비로 열심히 이관중인 신입입니다..
아래와같이 FULL OUTER JOIN의 쿼리가있는데, 마리아디비에선 어떻게 표현해야 할까요..?!
SELECT NVL(B1.SITE_CODE, B2.SITE_CODE) AS SITE_CODE, NVL(B1.YYYYMM, B2.ACT_YYYYMM) AS ACT_YYYYMM, B1.SCOPE_TYPE_CODE
, CASE WHEN COUNT(DECODE(B2.SITE_CODE, NULL, '', 1)) = 0 THEN '1'
WHEN COUNT(DECODE(B2.INPUT_FLAG, NULL, 1)) > 0 THEN '2'
ELSE '3'
END AS STATUS_CODE
FROM ( SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
FROM E05_ACT_MON_DATA A1
WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A1.SITE_CODE = '1'
) B2 FULL OUTER JOIN
( SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
FROM E05_ESRC A0
, E05_ESRC_DETAIL A1
, COPY_YYYYMM A2
WHERE A1.ESRC_CODE = A0.ESRC_CODE
AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
AND A1.SCOPE_TYPE_CODE IS NOT NULL
AND A0.SITE_CODE = '1'
) B1
ON B1.SITE_CODE = B2.SITE_CODE
AND B1.ESRC_CODE = B2.ESRC_CODE
AND B1.YYYYMM = B2.ACT_YYYYMM
AND B1.MET_CODE = B2.MET_CODE
AND B1.GHG_CODE = B2.GHG_CODE
AND B1.ENERGY_CODE = B2.ENERGY_CODE
AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
GROUP BY NVL(B1.SITE_CODE, B2.SITE_CODE), NVL(B1.YYYYMM, B2.ACT_YYYYMM), B1.SCOPE_TYPE_CODE;
by allen
[2021.08.25 10:22:25]
B2 LEFT JOIN B1
UNION
B2 RIGHT JOIN B2
by 꼬막
[2021.08.25 10:26:49]
SELECT *
FROM (
SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
FROM E05_ACT_MON_DATA A1
WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A1.SITE_CODE = '1'
) B2 LEFT JOIN
(
SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
FROM E05_ESRC A0
, E05_ESRC_DETAIL A1
, COPY_YYYYMM A2
WHERE A1.ESRC_CODE = A0.ESRC_CODE
AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
AND A1.SCOPE_TYPE_CODE IS NOT NULL
AND A0.SITE_CODE = '1'
) B1
ON B1.SITE_CODE = B2.SITE_CODE
AND B1.ESRC_CODE = B2.ESRC_CODE
AND B1.YYYYMM = B2.ACT_YYYYMM
AND B1.MET_CODE = B2.MET_CODE
AND B1.GHG_CODE = B2.GHG_CODE
AND B1.ENERGY_CODE = B2.ENERGY_CODE
AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
UNION
SELECT *
FROM (
SELECT A1.SITE_CODE, A1.ACT_YYYYMM, A1.SCOPE_TYPE_CODE, ESRC_CODE, ENERGY_CODE, GHG_CODE, INCI_CLASS_CODE, MET_CODE, A1.INPUT_FLAG
FROM E05_ACT_MON_DATA A1
WHERE A1.ACT_YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A1.SITE_CODE = '1'
) B2 RIGHT JOIN
(
SELECT A0.SITE_CODE, A2.YYYYMM, A1.SCOPE_TYPE_CODE, A1.ESRC_CODE, A1.ENERGY_CODE, A1.GHG_CODE, A1.INCI_CLASS_CODE, A1.MET_CODE
FROM E05_ESRC A0
, E05_ESRC_DETAIL A1
, COPY_YYYYMM A2
WHERE A1.ESRC_CODE = A0.ESRC_CODE
AND A2.YYYYMM BETWEEN '2021'||'01' AND '2021'||'12'
AND A2.YYYYMM BETWEEN A1.APPLY_START_YM AND A1.APPLY_END_YM
AND A1.SCOPE_TYPE_CODE IS NOT NULL
AND A0.SITE_CODE = '1'
) B1
ON B1.SITE_CODE = B2.SITE_CODE
AND B1.ESRC_CODE = B2.ESRC_CODE
AND B1.YYYYMM = B2.ACT_YYYYMM
AND B1.MET_CODE = B2.MET_CODE
AND B1.GHG_CODE = B2.GHG_CODE
AND B1.ENERGY_CODE = B2.ENERGY_CODE
AND B1.INCI_CLASS_CODE = B2.INCI_CLASS_CODE
;
이런식으로 사용했는데, 기존 FULL 데이터 갯수랑, 안맞는건 어쩔수없는걸까요..??
by 마농
[2021.08.25 11:09:38]
원본 쿼리 의문점(1) scope_type_code
- scope_type_code 는 조인 조건에 없네요?
- scope_type_code 는 SELECT 에서 NVL 처리를 안했네요?
원본 쿼리 의문점(2) status_code
- 1. b2 가 하나도 없으면(즉, b1 만 있으면) -> 1
- 2. b2 가 없는게 있으면 -> 2
- 3. 기타(b2 가 다 있는 경우) -> 3
- 이게 뭘 의미하는 건가요?
원본 쿼리 의문점(3)
- 전반적인 쿼리의 의미를 알 수 있을지?
- 두 집합 b1 과 b2 의 의미
- 두 집합 b1 과 b2 의 관계가 어떻게 되나요? (1:1) (1:다) (다:1) (다:다)
- full outer join 이 필요한 걸까요?
) 버튼을 클릭하여 작성 하시면 됩니다.