今天刷leetcode的sql题目,遇到一道题,
相当于要格式化整个列表,行转列。自然想到用id分组。
先放答案:
select id,
sum(case when month='Jan' then revenue end) Jan_Revenue,
sum(case when month='Feb' then revenue end) Feb_Revenue,
sum(case when month='Mar' then revenue end) Mar_Revenue,
sum(case when month='Apr' then revenue end) Apr_Revenue,
sum(case when month='May' then revenue end) May_Revenue,
sum(case when month='Jun' then revenue end) Jun_Revenue,
sum(case when month='Jul' then revenue end) Jul_Revenue,
sum(case when month='Aug' then revenue end) Aug_Revenue,
sum(case when month='Sep' then revenue end) Sep_Revenue,
sum(case when month='Oct' then revenue end) Oct_Revenue,
sum(case when month='Nov' then revenue end) Nov_Revenue,
sum(case when month='Dec' then revenue end) Dec_Revenue
from Department
group by id
注意到不能直接select需要的数据,而是需要加上sum,但实际上sum下都只有一条结果,为什么要这么做?
原因在于,原表进行group分组后,每条id下至多有12条结果,这时候,id列可以直接select,而revenue列不可以,只有聚合函数得出一条结果才可以被select,所以这里,用sum(),max()都可以,目的就是用case when筛出符合条件的那一条结果。