在复杂的数据分析和报表制作中，我们经常需要改变数据的展现形式。其中，最常见的需求就是行列转换和结果集的合并。掌握这些高级SQL技巧，能够极大地提升你解决复杂问题的能力。
思维导图
一、行列转换
1.1 行转列: 将多行数据聚合到单行多列
场景：假设你有一张学生成绩表，每条记录包含学生姓名、科目和分数。你希望生成一张报表，每行只显示一个学生，但列是语文、数学、英语等科目，值为对应的分数。
方法一：传统方式 (CASE WHEN + GROUP BY)这是 最通用、最经典的方法，适用于所有Oracle版本。
一般结构：
sql SELECT grouping_column, AGGREGATE_FUNCTION(CASE WHEN pivot_column = 'value1' THEN value_column END) AS new_column1, AGGREGATE_FUNCTION(CASE WHEN pivot_column = 'value2' THEN value_column END) AS new_column2, ... FROM source_table GROUP BY grouping_column;
grouping_column: 用于分组的列 (如学生姓名)。
AGGREGATE_FUNCTION: 聚合函数，如
MAX,
SUM,
AVG。对于
非数值或
唯一值的转换，通常使用
MAX 或
MIN。
pivot_column: 其值将决定新列名的列 (如科目)。
value_column:
要填充到新列中的
值所在的列 (如分数)。
代码案例：
假设有
student_scores 表
sql CREATE TABLE student_scores ( name VARCHAR2(100), subject VARCHAR2(50), score NUMBER );
sql -- 原始数据 -- NAME SUBJECT SCORE -- --------------------- -- Alice Math 90 -- Alice English 85 -- Bob Math 92 -- Bob English 88 SELECT name, MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score, MAX(CASE WHEN subject = 'English' THEN score END) AS english_score FROM student_scores GROUP BY name; -- 返回结果: -- NAME MATH_SCORE ENGLISH_SCORE -- ------------------------------- -- Alice 90 85 -- Bob 92 88
方法二：现代方式 (PIVOT 关键字, Oracle 11g+)
这是 更简洁、更具可读性的 专用语法。
一般结构：
sql SELECT * FROM ( -- 原始查询，选择需要的列 SELECT grouping_column, pivot_column, value_column FROM source_table ) PIVOT ( AGGREGATE_FUNCTION(value_column) FOR pivot_column IN ('value1' AS new_column1, 'value2' AS new_column2, ...) );
代码案例：
使用相同的
student_scores 表。
sql SELECT * FROM ( SELECT name, subject, score FROM student_scores ) PIVOT ( MAX(score) FOR subject IN ('Math' AS math_score, 'English' AS english_score) ); -- 返回结果与传统方式完全相同
### 1.2 列转行: 将单行多列数据拆分为多行
场景：与行转列相反。你有一张年度销售表，每行是一个产品，列分别是Q1销量、Q2销量、Q3销量、Q4销量。你希望将其转换为每行只包含产品、季度和对应销量的格式。
方法一：传统方式 (UNION ALL)直观易懂，但当列很多时 代码冗长。
一般结构：
sql SELECT identifier_column, 'value1_name' AS new_category_column, column_1 AS new_value_column FROM source_table UNION ALL SELECT identifier_column, 'value2_name' AS new_category_column, column_2 AS new_value_column FROM source_table UNION ALL ...
代码案例：
假设有
product_sales 表
sql CREATE TABLE product_sales ( product_name VARCHAR2(100), sales_q1 NUMBER, sales_q2 NUMBER );
sql -- 原始数据 -- PRODUCT_NAME SALES_Q1 SALES_Q2 -- -------------------------------- -- Laptop 100 120 -- Mouse 300 350 SELECT product_name, 'Q1' AS quarter, sales_q1 AS sales FROM product_sales UNION ALL SELECT product_name, 'Q2' AS quarter, sales_q2 AS sales FROM product_sales; -- 返回结果: -- PRODUCT_NAME QUARTER SALES -- ----------------------------- -- Laptop Q1 100 -- Laptop Q2 120 -- Mouse Q1 300 -- Mouse Q2 350
方法二：现代方式 (UNPIVOT 关键字, Oracle 11g+)
更简洁、高效。
一般结构：
sql SELECT * FROM source_table UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS] ( new_value_column FOR new_category_column IN (source_column1 AS 'value1_name', source_column2 AS 'value2_name', ...) );
INCLUDE NULLS: (默认是EXCLUDE NULLS) 如果源列的值为NULL，INCLUDE NULLS 会为它生成一行，EXCLUDE NULLS 则不会。代码案例：
使用相同的
product_sales 表```sql
SELECT
FROM product_sales
UNPIVOT (
sales
FOR quarter IN (sales_q1 AS 'Q1', sales_q2 AS 'Q2')
);
-- 返回结果与传统方式完全相同
```
## 二、集合运算
集合运算用于合并两个或多个 SELECT 语句的结果集。
基本规则：所有 SELECT 语句的列数量必须相同，且对应列的数据类型必须兼容。
背景表：假设有 class_a_students 和 class_b_students 两个表
CREATE TABLE class_a_students (
student_id INT,
student_name VARCHAR(50)
);
CREATE TABLE class_b_students (
student_id INT,
student_name VARCHAR(50)
);
2.1 UNION: 并集 (去重)
合并两个结果集，并 自动去除重复的行。sql SELECT student_id, student_name FROM class_a_students UNION SELECT student_id, student_name FROM class_b_students; -- 返回结果: 包含A班和B班的所有学生，每个学生只出现一次。
### 2.2 UNION ALL: 并集 (不去重)
合并两个结果集， 保留所有行，包括重复行。 性能通常比
UNION
更高。
sql SELECT student_id, student_name FROM class_a_students UNION ALL SELECT student_id, student_name FROM class_b_students; -- 返回结果: A班所有学生列表 + B班所有学生列表。如果一个学生同时在两个班，他会出现两次。
### 2.3 INTERSECT: 交集
返回同时 存在于两个结果集中的 行。
sql SELECT student_id, student_name FROM class_a_students INTERSECT SELECT student_id, student_name FROM class_b_students; -- 返回结果: 只返回那些既在A班又在B班的学生。
### 2.4 MINUS: 差集
返回存在于 第一个结果集但 不存在于第二个结果集中的行。 顺序很重要！
sql -- 查询只在A班，不在B班的学生 SELECT student_id, student_name FROM class_a_students MINUS SELECT student_id, student_name FROM class_b_students; -- 返回结果: 只属于A班的学生。
总结： 行列转换 (
CASE WHEN/
PIVOT,
UNION ALL/
UNPIVOT) 是
数据重塑的核心，而集合运算 (
UNION,
UNION ALL,
INTERSECT,
MINUS) 是
数据集整合的
关键。熟练运用这些工具，可以
优雅地解决许多
看似棘手的
数据处理难题。
---
## 练习题
背景表：
CREATE TABLE monthly_sales (
sales_person VARCHAR2(50 CHAR),
sale_month VARCHAR2(10 CHAR),
sale_amount NUMBER(12, 2)
);
CREATE TABLE quarterly_revenue (
product_line VARCHAR2(50 CHAR),
q1_revenue NUMBER(15, 2),
q2_revenue NUMBER(15, 2),
q3_revenue NUMBER(15, 2),
q4_revenue NUMBER(15, 2)
);
CREATE TABLE project_team_a (
employee_id NUMBER(10) NOT NULL,
employee_name VARCHAR2(50 CHAR) NOT NULL
);
CREATE TABLE project_team_b (
employee_id NUMBER(10),
employee_name VARCHAR2(50 CHAR)
);
请为以下每个场景编写相应的SQL语句。
题目：
- 行转列 (传统方式): 使用
monthly_sales表，查询每个销售员 (sales_person) 在 'Jan', 'Feb', 'Mar' 三个月的销售额。结果表应该有四列:sales_person,jan_sales,feb_sales,mar_sales。 - 行转列 (PIVOT): 使用与上一题相同的要求，但使用
PIVOT关键字实现。 - 列转行 (传统方式): 使用
quarterly_revenue表，将其转换为每行包含product_line,quarter(值为 'Q1', 'Q2', 'Q3', 'Q4'),revenue的格式。 - 列转行 (UNPIVOT): 使用与上一题相同的要求，但使用
UNPIVOT关键字实现。 - 并集去重: 查询所有参与过项目 (A或B) 的员工的
employee_id和employee_name，每个员工只显示一次。 - 并集不去重: 公司为每个项目组的成员发一份通知，需要一份包含项目A和项目B所有成员的完整名单 (允许重复)。请生成这个名单。
- 交集: 查询同时参与了项目A和项目B的员工。
- 差集: 查询只参与了项目A，但没有参与项目B的员工。
- 综合应用1 (行转列+聚合): 基于
monthly_sales表，计算每个销售员上半年的总销售额 (H1_Total) 和下半年的总销售额 (H2_Total)。假设 'Jan'到'Jun'为上半年，'Jul'到'Dec'为下半年。 - 综合应用2 (列转行+过滤): 使用
quarterly_revenue表，找出所有产品线中，哪个季度的收入 (revenue) 超过了 500000。结果需要显示product_line和quarter。
行转列 (传统方式):
SELECT sales_person, SUM(CASE WHEN sale_month = 'Jan' THEN sale_amount ELSE 0 END) AS jan_sales, SUM(CASE WHEN sale_month = 'Feb' THEN sale_amount ELSE 0 END) AS feb_sales, SUM(CASE WHEN sale_month = 'Mar' THEN sale_amount ELSE 0 END) AS mar_sales FROM monthly_sales GROUP BY sales_person;- 解析： 通过
GROUP BY sales_person对每个销售员进行分组。在SELECT子句中，CASE WHEN语句判断月份，如果匹配，则返回该月的销售额，否则返回0 (使用SUM聚合)。如果确定每个销售员每个月只有一条记录，也可以用MAX代替SUM和ELSE 0。
- 解析： 通过
行转列 (PIVOT):
SELECT * FROM ( SELECT sales_person, sale_month, sale_amount FROM monthly_sales ) PIVOT ( SUM(sale_amount) FOR sale_month IN ('Jan' AS jan_sales, 'Feb' AS feb_sales, 'Mar' AS mar_sales) );- 解析：
PIVOT语法更直观。SUM(sale_amount)是聚合函数，FOR sale_month指定了要转换的列，IN (...)列出了要成为新列标题的值及其别名。
- 解析：
列转行 (传统方式):
SELECT product_line, 'Q1' AS quarter, q1_revenue AS revenue FROM quarterly_revenue UNION ALL SELECT product_line, 'Q2' AS quarter, q2_revenue AS revenue FROM quarterly_revenue UNION ALL SELECT product_line, 'Q3' AS quarter, q3_revenue AS revenue FROM quarterly_revenue UNION ALL SELECT product_line, 'Q4' AS quarter, q4_revenue AS revenue FROM quarterly_revenue;- 解析： 为每个季度的列编写一个
SELECT语句，选取产品线、一个季度名称的字面量，以及该季度的收入列。然后使用UNION ALL将这四个结果集合并。
- 解析： 为每个季度的列编写一个
列转行 (UNPIVOT):
SELECT * FROM quarterly_revenue UNPIVOT ( revenue FOR quarter IN (q1_revenue AS 'Q1', q2_revenue AS 'Q2', q3_revenue AS 'Q3', q4_revenue AS 'Q4') );- 解析：
UNPIVOT语法更简洁。revenue是将要存放源列值的新列名，quarter是将要存放源列名(别名后)的新列名。IN (...)列出了要被转换的源列及其在新类别列中对应的字面值。
- 解析：
并集去重 (
UNION):SELECT employee_id, employee_name FROM project_team_a UNION SELECT employee_id, employee_name FROM project_team_b;- 解析：
UNION操作符合并两个查询的结果，并自动移除了同时存在于两个表中的重复员工记录。
- 解析：
并集不去重 (
UNION ALL):SELECT employee_id, employee_name FROM project_team_a UNION ALL SELECT employee_id, employee_name FROM project_team_b;- 解析：
UNION ALL简单地将两个查询的结果拼接在一起，如果一个员工在两个项目中，他的名字会出现两次。
- 解析：
交集 (
INTERSECT):SELECT employee_id, employee_name FROM project_team_a INTERSECT SELECT employee_id, employee_name FROM project_team_b;- 解析：
INTERSECT返回两个查询结果中共有的行，即同时在项目A和项目B中的员工。
- 解析：
差集 (
MINUS):SELECT employee_id, employee_name FROM project_team_a MINUS SELECT employee_id, employee_name FROM project_team_b;- 解析：
MINUS返回第一个查询结果中，未出现在第二个查询结果中的行。这里是只在项目A但不在项目B的员工。
- 解析：
综合应用1 (行转列+聚合):
SELECT sales_person, SUM(CASE WHEN sale_month IN ('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun') THEN sale_amount ELSE 0 END) AS h1_total, SUM(CASE WHEN sale_month IN ('Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') THEN sale_amount ELSE 0 END) AS h2_total FROM monthly_sales GROUP BY sales_person;- 解析： 这是一个行转列的应用变体。我们不是为每个月创建一列，而是根据月份属于上半年还是下半年，将销售额累加到
h1_total或h2_total这两列中。CASE WHEN和IN子句结合使用，实现了按条件分组聚合。
- 解析： 这是一个行转列的应用变体。我们不是为每个月创建一列，而是根据月份属于上半年还是下半年，将销售额累加到
综合应用2 (列转行+过滤):
SELECT product_line, quarter FROM ( SELECT * FROM quarterly_revenue UNPIVOT ( revenue FOR quarter IN (q1_revenue AS 'Q1', q2_revenue AS 'Q2', q3_revenue AS 'Q3', q4_revenue AS 'Q4') ) ) unpivoted_data WHERE unpivoted_data.revenue > 500000;- 解析： 首先，使用
UNPIVOT将宽表quarterly_revenue转换为长表格式。然后，将这个UNPIVOT操作的结果作为一个子查询 (或内联视图)unpivoted_data。最后，在外部查询中对这个转换后的结果集应用WHERE子句进行过滤。
- 解析： 首先，使用
