LeetCode 1179:重新格式化部门表(MySQL)

Question

这是一个面试的LeetCode问题。

编写此MySQL查询的最有效方法(时间/空间复杂度)是什么？编码风格是否遵循MySQL变量命名约定？

问题

表:部门

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+

(id，月份)是此表的主键。

该表提供了每个部门每月收入的信息。

月份的数值为“一月”、“二月”、“三月”、“四月”、“五月”、“六月”、“七月”、“八月”、“九月”、“十月”、“十一月”、“十二月”。

编写SQL查询以重新格式化表，使每个月都有一个部门id列和一个收入列。

查询结果格式如下所示：

部门表：

+------+---------+-------+
| 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列(部门id为1，月份为12 )。

Create table If Not Exists Department (id int, revenue int, month varchar(5))
Truncate table Department
insert into Department (id, revenue, month) values ('1', '8000', 'Jan')
insert into Department (id, revenue, month) values ('2', '9000', 'Jan')
insert into Department (id, revenue, month) values ('3', '10000', 'Feb')
insert into Department (id, revenue, month) values ('1', '7000', 'Feb')
insert into Department (id, revenue, month) values ('1', '6000', 'Mar')

尝试

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
ORDER BY id;

参考文献

1179.重新格式化部门表

Answer 1

我可能会使用MAX作为聚合函数，而不是SUM，假设给定的id和月份只有一个收入记录。尽管如此，如果您想使用SUM，那么您的CASE表达式应该有一个收入为零的其他条件：

SELECT
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS Feb_Revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS Mar_Revenue,
    SUM(CASE WHEN month = 'Apr' THEN revenue ELSE 0 END) AS Apr_Revenue,
    SUM(CASE WHEN month = 'May' THEN revenue ELSE 0 END) AS May_Revenue,
    SUM(CASE WHEN month = 'Jun' THEN revenue ELSE 0 END) AS Jun_Revenue,
    SUM(CASE WHEN month = 'Jul' THEN revenue ELSE 0 END) AS Jul_Revenue,
    SUM(CASE WHEN month = 'Aug' THEN revenue ELSE 0 END) AS Aug_Revenue,
    SUM(CASE WHEN month = 'Sep' THEN revenue ELSE 0 END) AS Sep_Revenue,
    SUM(CASE WHEN month = 'Oct' THEN revenue ELSE 0 END) AS Oct_Revenue,
    SUM(CASE WHEN month = 'Nov' THEN revenue ELSE 0 END) AS Nov_Revenue,
    SUM(CASE WHEN month = 'Dec' THEN revenue ELSE 0 END) AS Dec_Revenue
FROM department
GROUP BY
    id
ORDER BY
    id;