网站导航
> 技术文档 > Oracle 数据塑形：行列转换与集合运算

Oracle 数据塑形：行列转换与集合运算

网友: 技术文档 2025-07-30 13:07:35

作者：IvanCodes
日期：2025年7月2日
专栏：Oracle教程

在复杂的数据分析和报表制作中，我们经常需要改变数据的展现形式。其中，最常见的需求就是行列转换和结果集的合并。掌握这些高级SQL技巧，能够极大地提升你解决复杂问题的能力。

思维导图

Oracle 数据塑形：行列转换与集合运算
Oracle 数据塑形：行列转换与集合运算
Oracle 数据塑形：行列转换与集合运算

一、行列转换

1.1 行转列: 将多行数据聚合到单行多列

场景：假设你有一张学生成绩表，每条记录包含学生姓名、科目和分数。你希望生成一张报表，每行只显示一个学生，但列是语文、数学、英语等科目，值为对应的分数。

方法一：传统方式 (CASE WHEN + GROUP BY)
这是最通用、最经典的方法，适用于所有Oracle版本。

一般结构：

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_tableGROUP BY grouping_column;
  • grouping_column: 用于分组的列 (如学生姓名)。
  • AGGREGATE_FUNCTION: 聚合函数，如 MAX, SUM, AVG。对于非数值或唯一值的转换，通常使用 MAX 或 MIN。
  • pivot_column: 其值将决定新列名的列 (如科目)。
  • value_column: 要填充到新列中的值所在的列 (如分数)。

代码案例：
假设有 student_scores 表

CREATE TABLE student_scores ( name VARCHAR2(100), subject VARCHAR2(50), score NUMBER);
-- 原始数据-- NAME SUBJECT SCORE-- ----------------------- Alice Math 90-- Alice English 85-- Bob Math 92-- Bob English 88SELECT name, MAX(CASE WHEN subject = \'Math\' THEN score END) AS math_score, MAX(CASE WHEN subject = \'English\' THEN score END) AS english_scoreFROM student_scoresGROUP BY name;-- 返回结果:-- NAME MATH_SCORE ENGLISH_SCORE-- --------------------------------- Alice 90 85-- Bob 92 88

方法二：现代方式 (PIVOT 关键字, Oracle 11g+)
这是更简洁、更具可读性的专用语法。

一般结构：

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 表。

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)
直观易懂，但当列很多时代码冗长。

一般结构：

SELECT identifier_column, \'value1_name\' AS new_category_column, column_1 AS new_value_column FROM source_tableUNION ALLSELECT identifier_column, \'value2_name\' AS new_category_column, column_2 AS new_value_column FROM source_tableUNION ALL...

代码案例：
假设有 product_sales 表

CREATE TABLE product_sales ( product_name VARCHAR2(100), sales_q1 NUMBER, sales_q2 NUMBER);
-- 原始数据-- PRODUCT_NAME SALES_Q1 SALES_Q2-- ---------------------------------- Laptop 100 120-- Mouse 300 350SELECT product_name, \'Q1\' AS quarter, sales_q1 AS sales FROM product_salesUNION ALLSELECT 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+)
更简洁、高效。

一般结构：

SELECT *FROM source_tableUNPIVOT [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 表

SELECT *FROM product_salesUNPIVOT ( 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: 并集 (去重)

合并两个结果集，并自动去除重复的行。

SELECT student_id, student_name FROM class_a_studentsUNIONSELECT student_id, student_name FROM class_b_students;-- 返回结果: 包含A班和B班的所有学生，每个学生只出现一次。

2.2 UNION ALL: 并集 (不去重)

合并两个结果集，保留所有行，包括重复行。性能通常比 UNION 更高。

SELECT student_id, student_name FROM class_a_studentsUNION ALLSELECT student_id, student_name FROM class_b_students;-- 返回结果: A班所有学生列表 + B班所有学生列表。如果一个学生同时在两个班，他会出现两次。

2.3 INTERSECT: 交集

返回同时存在于两个结果集中的行。

SELECT student_id, student_name FROM class_a_studentsINTERSECTSELECT student_id, student_name FROM class_b_students;-- 返回结果: 只返回那些既在A班又在B班的学生。

2.4 MINUS: 差集

返回存在于第一个结果集但不存在于第二个结果集中的行。顺序很重要！

-- 查询只在A班，不在B班的学生SELECT student_id, student_name FROM class_a_studentsMINUSSELECT 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语句。

题目：

  1. 行转列 (传统方式): 使用 monthly_sales 表，查询每个销售员 (sales_person) 在 ‘Jan’, ‘Feb’, ‘Mar’ 三个月的销售额。结果表应该有四列: sales_person, jan_sales, feb_sales, mar_sales。
  2. 行转列 (PIVOT): 使用与上一题相同的要求，但使用 PIVOT 关键字实现。
  3. 列转行 (传统方式): 使用 quarterly_revenue 表，将其转换为每行包含 product_line, quarter (值为 ‘Q1’, ‘Q2’, ‘Q3’, ‘Q4’), revenue 的格式。
  4. 列转行 (UNPIVOT): 使用与上一题相同的要求，但使用 UNPIVOT 关键字实现。
  5. 并集去重: 查询所有参与过项目 (A或B) 的员工的 employee_id 和 employee_name，每个员工只显示一次。
  6. 并集不去重: 公司为每个项目组的成员发一份通知，需要一份包含项目A和项目B所有成员的完整名单 (允许重复)。请生成这个名单。
  7. 交集: 查询同时参与了项目A和项目B的员工。
  8. 差集: 查询只参与了项目A，但没有参与项目B的员工。
  9. 综合应用1 (行转列+聚合): 基于 monthly_sales 表，计算每个销售员上半年的总销售额 (H1_Total) 和下半年的总销售额 (H2_Total)。假设 \'Jan’到’Jun’为上半年，\'Jul’到’Dec’为下半年。
  10. 综合应用2 (列转行+过滤): 使用 quarterly_revenue 表，找出所有产品线中，哪个季度的收入 (revenue) 超过了 500000。结果需要显示 product_line 和 quarter。

答案与解析：

  1. 行转列 (传统方式):
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_salesFROM monthly_salesGROUP BY sales_person;
  • 解析： 通过 GROUP BY sales_person 对每个销售员进行分组。在 SELECT 子句中，CASE WHEN 语句判断月份，如果匹配，则返回该月的销售额，否则返回0 (使用SUM聚合)。如果确定每个销售员每个月只有一条记录，也可以用MAX代替SUM和ELSE 0。
  1. 行转列 (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 (...) 列出了要成为新列标题的值及其别名。
  1. 列转行 (传统方式):
SELECT product_line, \'Q1\' AS quarter, q1_revenue AS revenue FROM quarterly_revenueUNION ALLSELECT product_line, \'Q2\' AS quarter, q2_revenue AS revenue FROM quarterly_revenueUNION ALLSELECT product_line, \'Q3\' AS quarter, q3_revenue AS revenue FROM quarterly_revenueUNION ALLSELECT product_line, \'Q4\' AS quarter, q4_revenue AS revenue FROM quarterly_revenue;
  • 解析： 为每个季度的列编写一个 SELECT 语句，选取产品线、一个季度名称的字面量，以及该季度的收入列。然后使用 UNION ALL 将这四个结果集合并。
  1. 列转行 (UNPIVOT):
SELECT *FROM quarterly_revenueUNPIVOT ( revenue FOR quarter IN (q1_revenue AS \'Q1\', q2_revenue AS \'Q2\', q3_revenue AS \'Q3\', q4_revenue AS \'Q4\'));
  • 解析： UNPIVOT 语法更简洁。revenue 是将要存放源列值的新列名，quarter 是将要存放源列名(别名后)的新列名。IN (...) 列出了要被转换的源列及其在新类别列中对应的字面值。
  1. 并集去重 (UNION):
SELECT employee_id, employee_name FROM project_team_aUNIONSELECT employee_id, employee_name FROM project_team_b;
  • 解析： UNION 操作符合并两个查询的结果，并自动移除了同时存在于两个表中的重复员工记录。
  1. 并集不去重 (UNION ALL):
SELECT employee_id, employee_name FROM project_team_aUNION ALLSELECT employee_id, employee_name FROM project_team_b;
  • 解析： UNION ALL 简单地将两个查询的结果拼接在一起，如果一个员工在两个项目中，他的名字会出现两次。
  1. 交集 (INTERSECT):
SELECT employee_id, employee_name FROM project_team_aINTERSECTSELECT employee_id, employee_name FROM project_team_b;
  • 解析： INTERSECT 返回两个查询结果中共有的行，即同时在项目A和项目B中的员工。
  1. 差集 (MINUS):
SELECT employee_id, employee_name FROM project_team_aMINUSSELECT employee_id, employee_name FROM project_team_b;
  • 解析： MINUS 返回第一个查询结果中，未出现在第二个查询结果中的行。这里是只在项目A但不在项目B的员工。
  1. 综合应用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_totalFROM monthly_salesGROUP BY sales_person;
  • 解析： 这是一个行转列的应用变体。我们不是为每个月创建一列，而是根据月份属于上半年还是下半年，将销售额累加到 h1_total 或 h2_total 这两列中。CASE WHEN 和 IN 子句结合使用，实现了按条件分组聚合。
  1. 综合应用2 (列转行+过滤):
SELECT product_line, quarterFROM ( 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_dataWHERE unpivoted_data.revenue > 500000;
  • 解析： 首先，使用 UNPIVOT 将宽表 quarterly_revenue 转换为长表格式。然后，将这个 UNPIVOT 操作的结果作为一个子查询 (或内联视图) unpivoted_data。最后，在外部查询中对这个转换后的结果集应用 WHERE 子句进行过滤。
网络标签：

相关问题