꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 양식구성?

by 동동동 [SQL Query] [2019.12.02 09:59:35]

WITH TMP AS (
SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL
SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL
),
TMP2 AS (
SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL
)
SELECT TMP2.CD2, TMP2.CDNM, TMP.CD, TMP.CDNM 
 FROM TMP, TMP2
WHERE TMP.CD = TMP2.CD(+)

다음과 같은 자료에서

CD2	CDNM	CD	CDNM	종류
AA	사과	A	과일	Y
AA	사과	B	야채
AA	사과	C	고기
BB	당근	A	과일
BB	당근	B	야채	Y
BB	당근	C	고기

이런 포멧의 결과를 얻고 싶은데요...도움 부탁드립니다..요즘들어 문의가 많네요..ㅠㅠ

by 우리집아찌 [2019.12.02 10:10:19]


select tmp2.cd2 , tmp2.cdnm , tmp.cd , tmp.cdnm
        , case when tmp.cd = tmp2.cd then ‘y’ end as “종류”
  from tmp , tmp2

by 동동동 [2019.12.02 10:28:03]

우리집아찌 님 답글 감사드립니다..

만약 종류에 'Y' 대신에 Count를 한다면 쿼리도 달라져야 하겠죠?

WITH TMP AS (
SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL
SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL
),
TMP2 AS (
SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL
SELECT 'A' AS CD, 'AB' CD2, '사과' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL
)
SELECT TMP2.CD2, TMP2.CDNM, TMP.CD, TMP.CDNM 
 FROM TMP, TMP2
WHERE TMP.CD = TMP2.CD(+)

CDNM	CD	CDNM	COUNT
사과	A	과일	2
사과	B	야채
사과	C	고기
당근	A	과일
당근	B	야채	1
당근	C	고기

by 우리집아찌 [2019.12.02 10:12:45]

cross join (카타시안 곱) 쓰실때는 조심해야합다

m*n으로 데이터가 나와서 엄청난 결과값이 나올수 있어요 성능도 이슈가 있을수 있습니다

by 우리집아찌 [2019.12.02 10:46:54]


select tmp2.cdnm  , tmp.cd , tmp.cdnm , tmp2.cd
        , count(case when tmp.cd = tmp2.cd then ‘y’ end ) as cnt
  from tmp , tmp2
 group by tmp2.cdnm  , tmp.cd , tmp.cdnm , tmp2.cd
 order by tmp2.cd , tmp.cd

by 동동동 [2019.12.02 15:59:56]

우리집아찌님 답글 감사드립니다..꾸벅..

by 소주쵝오 [2019.12.04 17:07:59]

WITH TMP AS (
SELECT 'A' AS CD, '과일' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, '야채' AS CDNM FROM DUAL UNION ALL
SELECT 'C' AS CD, '고기' AS CDNM FROM DUAL
),
TMP2 AS (
SELECT 'A' AS CD, 'AA' CD2, '사과' AS CDNM FROM DUAL UNION ALL
SELECT 'B' AS CD, 'BB' CD2, '당근' AS CDNM FROM DUAL
)
SELECT MAX(TMP2.CD2) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CD2
     , MAX(TMP2.CDNM) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CDNM
     , TMP.CD
     , TMP.CDNM
     , DECODE(TMP.CD, TMP2.CD, 'Y') AS TYP
FROM TMP LEFT OUTER JOIN TMP2 PARTITION BY (TMP2.CD)
ON TMP.CD = TMP2.CD
;

SELECT CDNM1
     , CD1
     , CDNM2
     , COUNT(DECODE(TYP, 'Y', 1)) AS CNT
FROM (     
SELECT MAX(TMP2.CD2) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CD2
     , MAX(TMP2.CDNM) OVER(PARTITION BY TMP2.CD||TMP2.CD) AS CDNM1
     , TMP.CD AS CD1
     , TMP.CDNM AS CDNM2
     , DECODE(TMP.CD, TMP2.CD, 'Y') AS TYP
FROM TMP LEFT OUTER JOIN TMP2 PARTITION BY (TMP2.CD)
ON TMP.CD = TMP2.CD
)
GROUP BY CDNM1, CD1, CDNM2, CD2
ORDER BY CD2, CD1
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변