力扣题解:1179. 重新格式化部门表

发布于 2021-08-05 13:16 ，所属分类：试题库考试资料大全

❤️ 原题 ❤️

部门表 Department：

+---------------+---------+
|ColumnName|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个月份的收入列)。

☀️ 解题思路 ☀️

Pivot 函数简介

通过审题可以发现，需要将月份的值 12 个月转化为查询结果中的 12 个列。这就是明显的 行转列 格式化。

Oracle 11G 中出现的新特性 Pivot 行转列函数正好可以解此题。

下面先了解一下 Pivot 函数，主要用于进行行转列操作。

基本语法如下：

SELECT...
FROM...
PIVOT[XML]
(pivot_clause
pivot_for_clause
pivot_in_clause)
WHERE...

下面我们直接通过 LeetCode 题目实验来学习一下：

创建测试表 Department

CREATETABLEdepartment(
idNUMBER,
revenueNUMBER,
monthVARCHAR2(10)
);

插入测试数据

INSERTINTOdepartmentVALUES(1,8000,'Jan');
INSERTINTOdepartmentVALUES(1,7000,'Feb');
INSERTINTOdepartmentVALUES(1,6000,'Mar');
INSERTINTOdepartmentVALUES(2,9000,'Jan');
INSERTINTOdepartmentVALUES(3,10000,'Feb');
commit;

执行 Pivot 行转列函数

根据题意：已确定需要查出的列为 ID 和 12个月份，月份列对应的 REVENUE 的值需要进行汇总 (SUM) 显示。

SELECT*
FROMdepartment
PIVOT(SUM(revenue)as"Revenue"formonthin(
'Jan'as"Jan",
'Feb'as"Feb",
'Mar'as"Mar",
'Apr'as"Apr",
'May'as"May",
'Jun'as"Jun",
'Jul'as"Jul",
'Aug'as"Aug",
'Sep'as"Sep",
'Oct'as"Oct",
'Nov'as"Nov",
'Dec'as"Dec"
));