안녕하세요.
grade 테이블에 아래 데이터들이 있는데요!
얘네를 어찌해야 밑에 결과처럼 나올 수 있을까요? 참고자료라도 부탁드리겠습니다 ㅠㅠ
----------data-------
등급, 상태, 점수, id
A R 100 aa
B R 90 aa
C R 70 aa
D R 50 aa
A W 90 bb
B W 70 bb
C W 50 bb
----------결과---------
등급 R W
A 100 90
B 90 70
C 70 50
D 50 -
WITH T ( GRADE , STATUS , SCORE , ID ) AS ( SELECT 'A','R',100,'aa' FROM DUAL UNION ALL SELECT 'B','R',90 ,'aa' FROM DUAL UNION ALL SELECT 'C','R',70 ,'aa' FROM DUAL UNION ALL SELECT 'D','R',50 ,'aa' FROM DUAL UNION ALL SELECT 'A','W',90 ,'bb' FROM DUAL UNION ALL SELECT 'B','W',70 ,'bb' FROM DUAL UNION ALL SELECT 'C','W',50,'bb' FROM DUAL ) SELECT GRADE , MAX(SCORE) R , CASE WHEN ROW_NUMBER() OVER(ORDER BY GRADE DESC ) != 1 THEN MIN(SCORE) END W FROM T GROUP BY GRADE ORDER BY GRADE
//우리집아찌
감사합니다~! 주신 쿼리로 조금 응용을 해봤는데, bb의 60이 R에 출력이 되는데 W에 나오게 할수는 없을까요..?
WITH T ( GRADE , STATUS , SCORE , ID ) AS ( SELECT 'A','R',90,'aa' UNION ALL SELECT 'B','R',100 ,'aa' UNION ALL SELECT 'C','R',70 ,'aa' UNION ALL SELECT 'D','R',50 ,'aa' UNION ALL SELECT 'A','W',90 ,'bb' UNION ALL SELECT 'B','W',70 ,'bb' UNION ALL SELECT 'C','W',50, 'bb' UNION ALL SELECT 'D','W',20, 'bb' UNION ALL SELECT 'E','W',60, 'bb' UNION ALL SELECT 'A','O',90, 'cc' UNION ALL SELECT 'B','O',80, 'cc' UNION ALL SELECT 'C','O',70, 'cc') SELECT GRADE , MAX(SCORE) R , CASE WHEN ROW_NUMBER() OVER(ORDER BY GRADE DESC ) != 1 THEN MIN(SCORE) END W FROM T WHERE T.ID = 'aa' or T.ID = 'bb' GROUP BY GRADE ORDER BY GRADE
-- 위에것은 틀렸네요.. 상태값을 무시하고 되었네요. 다시 올립니다. WITH T ( GRADE , STATUS , SCORE , ID ) AS ( SELECT 'A','R',90,'aa' UNION ALL SELECT 'B','R',100 ,'aa' UNION ALL SELECT 'C','R',70 ,'aa' UNION ALL SELECT 'D','R',50 ,'aa' UNION ALL SELECT 'A','W',90 ,'bb' UNION ALL SELECT 'B','W',70 ,'bb' UNION ALL SELECT 'C','W',50, 'bb' UNION ALL SELECT 'D','W',20, 'bb' UNION ALL SELECT 'E','W',60, 'bb' UNION ALL SELECT 'A','O',90, 'cc' UNION ALL SELECT 'B','O',80, 'cc' UNION ALL SELECT 'C','O',70, 'cc') SELECT GRADE , CASE WHEN MAX(CASE WHEN STATUS='R' THEN SCORE END ) IS NULL THEN MIN(CASE WHEN STATUS='W' THEN SCORE END) ELSE MAX(CASE WHEN STATUS='R' THEN SCORE END ) END R , CASE WHEN ROW_NUMBER() OVER(ORDER BY GRADE DESC ) != 1 THEN MIN(CASE WHEN STATUS='W' THEN SCORE END) END W FROM T WHERE T.ID = 'aa' or T.ID = 'bb' GROUP BY GRADE ORDER BY GRADE
-- 제가 잘못이해했나보네요.
WITH T ( GRADE , STATUS , SCORE , ID ) AS ( SELECT 'A','R',90,'aa' UNION ALL SELECT 'B','R',100 ,'aa' UNION ALL SELECT 'C','R',70 ,'aa' UNION ALL SELECT 'D','R',50 ,'aa' UNION ALL SELECT 'A','W',90 ,'bb' UNION ALL SELECT 'B','W',70 ,'bb' UNION ALL SELECT 'C','W',50, 'bb' UNION ALL SELECT 'D','W',20, 'bb' UNION ALL SELECT 'E','W',60, 'bb' UNION ALL SELECT 'A','O',90, 'cc' UNION ALL SELECT 'B','O',80, 'cc' UNION ALL SELECT 'C','O',70, 'cc') SELECT GRADE , MAX(CASE WHEN STATUS='R' THEN SCORE END) R , MIN(CASE WHEN STATUS='W' THEN SCORE END) W FROM T WHERE T.ID = 'aa' or T.ID = 'bb' GROUP BY GRADE ORDER BY GRADE