by HSky [SQL Query] query plsql sql [2022.11.29 11:58:19]
오전이라 머리가 굳었는지 쿼리가 짜여지질 않네요 도움 부탁드립니다..ㅠ
A 테이블에 code라는 컬럼이 있고
1 2 3 4 5 | code ------- A B C |
, B 테이블에 name, code, score 라는 컬럼이 있습니다.
1 2 3 4 5 6 7 8 9 10 | name | code | score ---------------------------------- 홍길동 | A | 1 홍길동 | B | 5 영희 | A | 2 철수 | A | 3 철수 | C | 8 B 테이블의 code와 A 테이블의 code 를 비교하여 A 테이블의 code 값 만큼, 그리고 B 테이블의 name 값 만큼 조회를 하려합니다. 만약 B 테이블의 특정 name 으로 조회된 결과의 code가 A 테이블의 code값만큼 없다면 score를 0으로 nvl처리 하려합니다. |
1 2 3 4 5 6 7 8 9 10 11 | name | code | score ---------------------------------- 홍길동 | A | 1 홍길동 | B | 5 홍길동 | C | 0 영희 | A | 2 영희 | B | 0 영희 | C | 0 철수 | A | 3 철수 | B | 0 철수 | C | 8 |
B테이블의 홍길동(name)의 code는 A 테이블의 code에 있는 A, B값을 가지고 있으니 score는 1,5가 조회되지만 C값은 없으니 0으로,
영희는 B,C 가 없으니 B,C의 score는 0으로 처리하려는 식입니다.
이걸 다시 행을 열로 pivot 처리 하려합니다....만 도저히 감이 오질 않습니다.
1 2 3 4 5 | name | score_A | score_B | score_C ----------------------------------------------------- 홍길동 1 5 0 영희 2 0 0 철수 3 0 8 |
도움을 요청합니다..!
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | -- 1번 WITH A_TBL AS ( SELECT 'A' AS COD FROM DUAL UNION ALL SELECT 'B' AS COD FROM DUAL UNION ALL SELECT 'C' AS COD FROM DUAL ) , B_TBL AS ( SELECT '홍길동' AS NM, 'A' AS COD, 1 AS SCORE FROM DUAL UNION ALL SELECT '홍길동' AS NM, 'B' AS COD, 5 AS SCORE FROM DUAL UNION ALL SELECT '영희' AS NM, 'A' AS COD, 2 AS SCORE FROM DUAL UNION ALL SELECT '철수' AS NM, 'A' AS COD, 3 AS SCORE FROM DUAL UNION ALL SELECT '철수' AS NM, 'C' AS COD, 8 AS SCORE FROM DUAL ) , C_TBL AS ( SELECT B.NM , DECODE(B.COD, 'A' , B.SCORE, 0) AS SCORE_A , DECODE(B.COD, 'B' , B.SCORE, 0) AS SCORE_B , DECODE(B.COD, 'C' , B.SCORE, 0) AS SCORE_C FROM A_TBL A , B_TBL B WHERE B.COD = A.COD ) SELECT NM , SUM (SCORE_A) AS SCORE_A , SUM (SCORE_B) AS SCORE_B , SUM (SCORE_C) AS SCORE_C FROM C_TBL GROUP BY NM ; -- 2번 WITH A_TBL AS ( SELECT 'A' AS COD FROM DUAL UNION ALL SELECT 'B' AS COD FROM DUAL UNION ALL SELECT 'C' AS COD FROM DUAL ) , B_TBL AS ( SELECT '홍길동' AS NM, 'A' AS COD, 1 AS SCORE FROM DUAL UNION ALL SELECT '홍길동' AS NM, 'B' AS COD, 5 AS SCORE FROM DUAL UNION ALL SELECT '영희' AS NM, 'A' AS COD, 2 AS SCORE FROM DUAL UNION ALL SELECT '철수' AS NM, 'A' AS COD, 3 AS SCORE FROM DUAL UNION ALL SELECT '철수' AS NM, 'C' AS COD, 8 AS SCORE FROM DUAL ) SELECT NM , NVL(SCORE_A, 0) AS SCORE_A , NVL(SCORE_B, 0) AS SCORE_B , NVL(SCORE_C, 0) AS SCORE_C FROM ( SELECT NM , COD , SCORE FROM B_TBL ) PIVOT ( SUM (SCORE) FOR COD IN ( 'A' AS SCORE_A, 'B' AS SCORE_B, 'C' AS SCORE_C) ) ; |
무조건 score_A, score_B, score_C 로 표현하는 거면 A테이블이 없어도 될것 같네요..
1. 중간 과정은 오히려 불필요한 과정입니다.
- 없는 코드값 0 을 추가하는게 오히려 더 어렵습니다.
- 중간 과정 없이 바로 뽑는게 오히려 더 쉽습니다.
2. 행을 열로 바꿀 때
- 항목의 구성을 동적으로 하는 것은 불가능합니다.
- 정해진 수의 정해진 값을 기준으로만 가능합니다.
- 동적으로 구현하는 것이 가능하긴 한데 그건 SQL 영역이 아닌 프로그래밍 영역입니다.(동적쿼리)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -- GROUP BY ~ DECODE -- SELECT name , NVL( MIN (DECODE(code, 'A' , score)), 0) a , NVL( MIN (DECODE(code, 'B' , score)), 0) b , NVL( MIN (DECODE(code, 'C' , score)), 0) c FROM t_data b GROUP BY name ; -- PIVOT -- SELECT * FROM t_data b PIVOT ( MIN (score) FOR code IN ( 'A' a, 'B' b, 'C' c)) ; -- 중간과정(참고용) -- SELECT b. name , a.code , NVL(b.score, 0) score FROM t_code a LEFT OUTER JOIN t_data b PARTITION BY (b. name ) ON a.code = b.code ORDER BY name , code ; |
오라클에서 직접 테스트 해본 오라클 쿼리입니다.
LEFT OUTER JOIN 자체는 ANSI 인데
PARTITION BY 가 ANSI 인지는 잘 모르겠습니다.(오라클(10G 이상)에서만 동작함, 타 DB 는 기능 없는 것으로 압니다. 아마도?)
http://gurubee.net/lecture/2204