内容阅读

在知识库中专注阅读，并随时返回相关工具与课程

运维管理7 分钟阅读

⭐️ LeetCode 解题系列 ⭐️ 1179. 重新格式化部门表（Oracle Pivot 行转列函数）

@TOC1179. 重新格式化部门表部门表 Department： +---------------+---------+ Column Name Type +---------------+---------+ id int revenue int month varchar +--------

2021年7月31日

oraclerac

DBA 实战课程

系统学习 Oracle DBA

2738+ 篇实战文章
1500+ 步骤截图
10 大核心模块

开通会员 ¥128/月起

查看完整课程

返回博客列表

运维管理7 分钟阅读

⭐️ LeetCode 解题系列 ⭐️ 1179. 重新格式化部门表（Oracle Pivot 行转列函数）

@TOC1179. 重新格式化部门表部门表 Department： +---------------+---------+ Column Name Type +---------------+---------+ id int revenue int month varchar +--------

2021年7月31日

oraclerac

❤️ 原题 ❤️

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

部门表 Department：

1+---------------+---------+
2Column Name / Type
3+---------------+---------+
4id / int
5revenue / int
6month / varchar
7+---------------+---------+
8(id, month) 是表的联合主键。
9这个表格有关于每个部门每月收入的信息。
10月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","No

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

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

1Department 表：
2+------+---------+-------+
3| id   | revenue | month |
4+------+---------+-------+
5| 1    | 8000    | Jan   |
6| 2    | 9000    | Jan   |
7| 3    | 10000   | Feb   |
8| 1    | 7000    | Feb   |
9| 1    | 6000    | Mar   |
10+------+---------+-------+
11
12查询得到的结果表：
13+------+-------------+-------------+-------------+-----+-------------+
14| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
15+------+-------------+-------------+-------------+-----+-------------+
16| 1    | 8000        | 7000        | 6000        | ... | null        |
17| 2    | 9000        | null        | null        | ... | null        |
18| 3    | null        | 10000       | null        | ... | null        |
19+------+-------------+-------------+-------------+-----+-------------+
20
21注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

☀️ 解题思路 ☀️

Pivot 函数简介

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

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

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

基本语法如下：

SQL

1SELECT ...
2FROM   ...
3PIVOT [XML]
4   ( pivot_clause
5     pivot_for_clause
6     pivot_in_clause )
7WHERE  ...

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

创建测试表 Department

SQL

1CREATE TABLE department (
2  id        NUMBER,
3  revenue   NUMBER,
4  month  VARCHAR2(10)
5);

在这里插入图片描述

插入测试数据

SQL

1INSERT INTO department VALUES (1, 8000, 'Jan');
2INSERT INTO department VALUES (1, 7000, 'Feb');
3INSERT INTO department VALUES (1, 6000, 'Mar');
4INSERT INTO department VALUES (2, 9000, 'Jan');
5INSERT INTO department VALUES (3, 10000, 'Feb');
6commit;

在这里插入图片描述

执行 Pivot 行转列函数

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

SQL

1SELECT *
2FROM department
3PIVOT (SUM(revenue) as "Revenue" for month in (
4'Jan' as "Jan",
5'Feb' as "Feb",
6'Mar' as "Mar",
7'Apr' as "Apr",
8'May' as "May",
9'Jun' as "Jun",
10'Jul' as "Jul",
11'Aug' as "Aug",
12'Sep' as "Sep",
13'Oct' as "Oct",
14'Nov' as "Nov",
15'Dec' as "Dec"
16));

在这里插入图片描述 ❤️ 格式化结果与原题所需要求保持一致，解题完成。 ❤️

在这里插入图片描述

❄️ 写在最后

关于 Pivot 行转列函数，还有 UNPivot 函数，感兴趣的朋友可以翻阅官方文档，或者参考以下文章：

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1

本次分享到此结束啦~

如果觉得文章对你有帮助，点赞、收藏、关注、评论，一键四连支持，你的支持就是我创作最大的动力。

❤️ 技术交流可以关注公众号：Lucifer三思而后行 ❤️

Lucifer三思而后行

DBA 实战课程

系统学习 Oracle DBA

2738+ 篇实战文章
1500+ 步骤截图
10 大核心模块

开通会员 ¥128/月起

查看完整课程

❤️ 原题 ❤️

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

部门表 Department：

1+---------------+---------+
2Column Name / Type
3+---------------+---------+
4id / int
5revenue / int
6month / varchar
7+---------------+---------+
8(id, month) 是表的联合主键。
9这个表格有关于每个部门每月收入的信息。
10月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","No

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

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

1Department 表：
2+------+---------+-------+
3| id   | revenue | month |
4+------+---------+-------+
5| 1    | 8000    | Jan   |
6| 2    | 9000    | Jan   |
7| 3    | 10000   | Feb   |
8| 1    | 7000    | Feb   |
9| 1    | 6000    | Mar   |
10+------+---------+-------+
11
12查询得到的结果表：
13+------+-------------+-------------+-------------+-----+-------------+
14| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
15+------+-------------+-------------+-------------+-----+-------------+
16| 1    | 8000        | 7000        | 6000        | ... | null        |
17| 2    | 9000        | null        | null        | ... | null        |
18| 3    | null        | 10000       | null        | ... | null        |
19+------+-------------+-------------+-------------+-----+-------------+
20
21注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

☀️ 解题思路 ☀️

Pivot 函数简介

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

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

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

基本语法如下：

SQL

1SELECT ...
2FROM   ...
3PIVOT [XML]
4   ( pivot_clause
5     pivot_for_clause
6     pivot_in_clause )
7WHERE  ...

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

创建测试表 Department

SQL

1CREATE TABLE department (
2  id        NUMBER,
3  revenue   NUMBER,
4  month  VARCHAR2(10)
5);

在这里插入图片描述

⭐️ LeetCode 解题系列 ⭐️ 1179. 重新格式化部门表（Oracle Pivot 行转列函数） - DBA 学习之路