Oracle 数据塑形：行列转换与集合运算-EW帮帮网

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

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

思维导图

在这里插入图片描述

一、行列转换

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_table
GROUP 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  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+)
这是更简洁、更具可读性的专用语法。

一般结构：

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

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       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+)
更简洁、高效。

一般结构：

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 表

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: 并集 (去重)

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

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 更高。

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: 交集

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

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

2.4 MINUS: 差集

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

-- 查询只在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 子句进行过滤。

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

思维导图

一、行列转换

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

1.2 列转行: 将单行多列数据拆分为多行

二、集合运算

2.1 UNION: 并集 (去重)

2.2 UNION ALL: 并集 (不去重)

2.3 INTERSECT: 交集

2.4 MINUS: 差集

练习题

网站公告

今日签到

热门文章

最新发布