노트1/SQL
[MySQL] LeetCode 문제풀이 : CASE문 테이블피봇 (1179. Reformat Department Table)
Paige09
2021. 12. 21. 15:22
https://leetcode.com/problems/reformat-department-table/
Reformat Department Table - LeetCode
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
leetcode.com
확실히 처음 풀었을 때 보다는 풀이 시간이 단축됐다!
처음에 sum(revenue)로 접근했는데, SELECT 절이어서 CASE문 내에서 id별 합산이 이루어지지 않았다.
SELECT id,
CASE WHEN month = 'Jan' THEN sum(revenue) END as Jan_Revenue
FROM Department
GROUP BY id
SUM(CASE문 - revenue 출력) 으로 해결.
SELECT id,
SUM(CASE WHEN month = 'Jan' THEN revenue END) as Jan_Revenue,
SUM(CASE WHEN month = 'Feb' THEN revenue END) as Feb_Revenue,
SUM(CASE WHEN month = 'Mar' THEN revenue END) as Mar_Revenue,
SUM(CASE WHEN month = 'Apr' THEN revenue END) as Apr_Revenue,
SUM(CASE WHEN month = 'May' THEN revenue END) as May_Revenue,
SUM(CASE WHEN month = 'Jun' THEN revenue END) as Jun_Revenue,
SUM(CASE WHEN month = 'Jul' THEN revenue END) as Jul_Revenue,
SUM(CASE WHEN month = 'Aug' THEN revenue END) as Aug_Revenue,
SUM(CASE WHEN month = 'Sep' THEN revenue END) as Sep_Revenue,
SUM(CASE WHEN month = 'Oct' THEN revenue END) as Oct_Revenue,
SUM(CASE WHEN month = 'Nov' THEN revenue END) as Nov_Revenue,
SUM(CASE WHEN month = 'Dec' THEN revenue END) as Dec_Revenue
FROM Department
GROUP BY id
인프런 강의를 확인하니 ELSE NULL 까지 써주면 더 좋을 듯 하다. :)
SELECT id,
SUM(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) as Jan_Revenue,
SUM(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) as Feb_Revenue,
SUM(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) as Mar_Revenue,
SUM(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) as Apr_Revenue,
SUM(CASE WHEN month = 'May' THEN revenue ELSE NULL END) as May_Revenue,
SUM(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) as Jun_Revenue,
SUM(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) as Jul_Revenue,
SUM(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) as Aug_Revenue,
SUM(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) as Sep_Revenue,
SUM(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) as Oct_Revenue,
SUM(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) as Nov_Revenue,
SUM(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) as Dec_Revenue
FROM Department
GROUP BY id