SQL面试题--行转列及case when的使用

重新格式化部门表
部门表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

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;

CASE语句的写法

CASE 
WHEN <求值表达式> THEN <表达式1>
WHEN <求值表达式> THEN <表达式2>
ELSE <表达式>
END

case应用

1、添加列

先在需要根据生日列 **生成新的一列 **：显示90后，00后，10后
代码：

SELECT 
s_name
,s_birthday 
,CASE 
        WHEN YEAR(s_birthday)>=1990 and YEAR(s_birthday)<2000 THEN "90后"
        WHEN YEAR(s_birthday)>=2000 and YEAR(s_birthday)<2010 THEN "00后"
        WHEN YEAR(s_birthday)>=2010 and YEAR(s_birthday)<2020 THEN "10后"
    ELSE "未知"
    END 
    AS "阶段"
from student ;

显示结果

2、行转化列

如上

3、分组统计

SELECT 
CASE 
        WHEN score<60 THEN "不及格"
        WHEN score>=60 and score<85 THEN "良"
        WHEN score>=85 THEN "优秀"
    ELSE "未知"   END     AS "阶段"
  ,count(*) as "人次"
from  score a INNER JOIN student b  on a.s_id=b.s_id
GROUP BY CASE 
        WHEN score<60 THEN "不及格"
        WHEN score>=60 and score<85 THEN "良"
        WHEN score>=85 THEN "优秀"
    ELSE "未知"
    END ;