SQL 变换结果集成多行,你想把行变换成列,并根据指定列的值来决定每一行原来的数据要被划分到新数据的哪一列。然而,与前一个实例不同的是,你需要输出的结果不止一行。
SQL 变换结果集成多行 问题描述
你想把行变换成列,并根据指定列的值来决定每一行原来的数据要被划分到新数据的哪一列。然而,与前一个实例不同的是,你需要输出的结果不止一行。
例如,你希望返回每个员工和他们的职位(对应 EMP
表的 JOB
列),你先查询得到了下面的结果集。
JOB ENAME
--------- ----------
ANALYST SCOTT
ANALYST FORD
CLERK SMITH
CLERK ADAMS
CLERK MILLER
CLERK JAMES
MANAGER JONES
MANAGER CLARK
MANAGER BLAKE
PRESIDENT KING
SALESMAN ALLEN
SALESMAN MARTIN
SALESMAN TURNER
SALESMAN WARD
你希望格式化上述结果集,为每一种职位生成一列新数据。
CLERKS ANALYSTS MGRS PREZ SALES
------ -------- ----- ---- ------
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN
SQL 变换结果集成多行 解决方案
与 12.1 节中的实例不同,本实例最后输出的结果集会多于一行。因此,12.1 节中实例的做法不适用于本例,因为如果针对每一个 JOB
列执行 MAX(ENAME)
的话,会导致每个 JOB
只对应一个 ENAME
(也就是说,就会像 12.1 节中的实例那样只返回一行结果)。为了解决这一问题,我们必须保持每一个 JOB/ENAME
组合的唯一性。这样一来,再调用聚合函数剔除掉 Null
的话,就不会丢失任何一个 ENAME
了。
DB2、Oracle 和 SQL Server
使用窗口函数 ROW_NUMBER OVER
确保每一个 JOB/ENAME
组合都是唯一的。针对窗口函数的返回值执行 GROUP BY
,并使用 CASE
表达式和聚合函数 MAX
实现结果集变换。
1 select max(case when job='CLERK'
2 then ename else null end) as clerks,
3 max(case when job='ANALYST'
4 then ename else null end) as analysts,
5 max(case when job='MANAGER'
6 then ename else null end) as mgrs,
7 max(case when job='PRESIDENT'
8 then ename else null end) as prez,
9 max(case when job='SALESMAN'
10 then ename else null end) as sales
11 from (
12 select job,
13 ename,
14 row_number()over(partition by job order by ename) rn
15 from emp
16 ) x
17 group by rn
PostgreSQL 和 MySQL
使用标量子查询基于 EMPNO
为每个员工排序。针对标量子查询的返回值执行 GROUP BY
,并使用 CASE
表达式和聚合函数 MAX
实现结果集变换。
1 select max(case when job='CLERK'
2 then ename else null end) as clerks,
3 max(case when job='ANALYST'
4 then ename else null end) as analysts,
5 max(case when job='MANAGER'
6 then ename else null end) as mgrs,
7 max(case when job='PRESIDENT'
8 then ename else null end) as prez,
9 max(case when job='SALESMAN'
10 then ename else null end) as sales
11 from (
12 select e.job,
13 e.ename,
14 (select count(*) from emp d
15 where e.job=d.job and e.empno < d.empno) as rnk
16 from emp e
17 ) x
18 group by rnk
SQL 变换结果集成多行 扩展知识
DB2、Oracle 和 SQL Server
首先,使用窗口函数 ROW_NUMBER OVER
确保每一个 JOB/ENAME
组合的唯一性。
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
JOB ENAME RN
--------- ---------- ----------
ANALYST FORD 1
ANALYST SCOTT 2
CLERK ADAMS 1
CLERK JAMES 2
CLERK MILLER 3
CLERK SMITH 4
MANAGER BLAKE 1
MANAGER CLARK 2
MANAGER JONES 3
PRESIDENT KING 1
SALESMAN ALLEN 1
SALESMAN MARTIN 2
SALESMAN TURNER 3
SALESMAN WARD 4
对于一种给定的职位,为其中的每一个 ENAME
安排一个唯一的“行编号”,这样即使出现了两个员工具有相同名字和职位的情况也不会有问题。这样做是为了既能基于行编号(RN
)分组,又不会因为使用了 MAX
而遗漏掉任何一个员工。这是解决本问题最重要的一步。如果没有这一步,外层查询的聚合操作会剔除掉必要的行。试想一下,如果仍然采用 12.1 节中实例的做法,而不使用 ROW_NUMBER OVER
函数的话,会得到怎样的结果集。
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from emp
CLERKS ANALYSTS MGRS PREZ SALES
---------- ---------- ---------- ---------- ----------
SMITH SCOTT JONES KING WARD
很不幸,每一种 JOB
只会返回一行:ENAME
值最大的那个员工会被返回。做结果集变换的时候,使用 MIN
或 MAX
是为了从结果集里剔除掉 Null
值,而不是为了过滤掉一些 ENAME
。继续阅读下面的讲解内容,相信你会更加清楚地理解这种状况是如何产生的。
下一步使用 CASE
表达式把 ENAME
分别放入各自的列(JOB
)。
select rn,
case when job='CLERK'
then ename else null end as clerks,
case when job='ANALYST'
then ename else null end as analysts,
case when job='MANAGER'
then ename else null end as mgrs,
case when job='PRESIDENT'
then ename else null end as prez,
case when job='SALESMAN'
then ename else null end as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x
RN CLERKS ANALYSTS MGRS PREZ SALES
-- ---------- ---------- ---------- ---------- ----------
1 FORD
2 SCOTT
1 ADAMS
2 JAMES
3 MILLER
4 SMITH
1 BLAKE
2 CLARK
3 JONES
1 KING
1 ALLEN
2 MARTIN
3 TURNER
4 WARD
现在,行已经变成了列,最后需要剔除掉 Null
值以提高结果集的可读性。为了剔除掉 Null
,需要调用聚合函数 MAX
,并基于 RN
执行 GROUP BY
。(这里也可以使用 MIN
函数。选择 MAX
或者 MIN
没有区别,因为每个分组只包含一个值。)每个 RN/JOB/ENAME
组合只包含一个值。基于 RN
执行 GROUP BY
,并且针对 CASE
表达式的返回值调用 MAX
函数,这能够确保每一次调用 MAX
函数都会从一个分组中取出一个 ENAME
,而该分组内除了该 ENAME
之外,其他值都是 Null
。
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select job,
ename,
row_number()over(partition by job order by ename) rn
from emp
) x
group by rn
CLERKS ANALYSTS MGRS PREZ SALES
------ -------- ----- ---- ------
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN
使用 ROW_NUMBER OVER
生成唯一的组合对于格式化查询结果集非常有用。考虑如下所示的查询,它创建了一个分别以 DEPTNO
和 JOB
为维度展示员工的稀疏矩阵报表。
select deptno dno, job,
max(case when deptno=10
then ename else null end) as d10,
max(case when deptno=20
then ename else null end) as d20,
max(case when deptno=30
then ename else null end) as d30,
max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as anals,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select deptno,
job,
ename,
row_number()over(partition by job order by ename) rn_job,
row_number()over(partition by job order by ename) rn_deptno
from emp
) x
group by deptno, job, rn_deptno, rn_job
order by 1
DNO JOB D10 D20 D30 CLERKS ANALS MGRS PREZ SALES
--- --------- ------ ----- ------ ------ ----- ----- ---- ------
10 CLERK MILLER MILLER
10 MANAGER CLARK CLARK
10 PRESIDENT KING KING
20 ANALYST FORD FORD
20 ANALYST SCOTT SCOTT
20 CLERK ADAMS ADAMS
20 CLERK SMITH SMITH
20 MANAGER JONES JONES
30 CLERK JAMES JAMES
30 MANAGER BLAKE BLAKE
30 SALESMAN ALLEN ALLEN
30 SALESMAN MARTIN MARTIN
30 SALESMAN TURNER TURNER
30 SALESMAN WARD WARD
通过改变分组列(即上述 SELECT
列表里的非聚合项),可以产生出不同格式的报表。这值得我们花时间去做一些实验,并深入理解 GROUP BY
子句包含的项目发生了改动之后,输出结果的格式将会发生什么变化。
PostgreSQL 和 MySQL
对于这两种数据库而言,做法和其他数据库基本相同,只是创建唯一 JOB/ENAME
组合的方法有所不同。首先使用标量子查询为每一个 JOB/ENAME
组合提供一个“行编号”或“排名”。
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk
from emp e
JOB ENAME RNK
--------- ---------- ----------
CLERK SMITH 3
SALESMAN ALLEN 3
SALESMAN WARD 2
MANAGER JONES 2
SALESMAN MARTIN 1
MANAGER BLAKE 1
MANAGER CLARK 0
ANALYST SCOTT 1
PRESIDENT KING 0
SALESMAN TURNER 0
CLERK ADAMS 2
CLERK JAMES 1
ANALYST FORD 0
CLERK MILLER 0
为每一个 JOB/ENAME
组合安排一个唯一的“行编号”,这可以确保每一行都是唯一的。即使有两个员工名字相同,其职位也恰好一样,他们也不会共享同一个“行编号”。这是解决本问题最重要的一步。如果没有这一步,外层查询的聚合操作会剔除掉必要的行。试想一下,如果仍然采用 12.1 节中实例的做法,而不为每一个 JOB/ENAME
组合安排一个唯一的“行编号”的话,会得到怎样的结果集。
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from emp
CLERKS ANALYSTS MGRS PREZ SALES
---------- ---------- ---------- ---------- ----------
SMITH SCOTT JONES KING WARD
很不幸,每一种 JOB
只会返回一行:ENAME
值最大的那个员工会被返回。做结果集变换的时候,使用 MIN
或 MAX
是为了从结果集里剔除掉 Null
值,而不是为了过滤掉一些 ENAME
。
现在,我们已经充分了解了“行编号”的作用,可以接着做下一步了。下一步使用 CASE
表达式把 ENAME
分别放入各自的列(JOB
)。
select rnk,
case when job='CLERK'
then ename else null end as clerks,
case when job='ANALYST'
then ename else null end as analysts,
case when job='MANAGER'
then ename else null end as mgrs,
case when job='PRESIDENT'
then ename else null end as prez,
case when job='SALESMAN'
then ename else null end as sales
from (
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk
from emp e
) x
RNK CLERKS ANALYSTS MGRS PREZ SALES
--- ------ -------- ----- ---- ----------
3 SMITH
3 ALLEN
2 WARD
2 JONES
1 MARTIN
1 BLAKE
0 CLARK
1 SCOTT
0 KING
0 TURNER
2 ADAMS
1 JAMES
0 FORD
0 MILLER
现在,行已经变成了列,最后需要剔除掉 Null
值以提高结果集的可读性。为了剔除掉 Null
,要调用聚合函数 MAX
,并且基于 RNK
执行 GROUP BY
。(这里也可以使用 MIN
函数,选择 MAX
或者 MIN
没有区别。)每个 RNK/JOB/ENAME
组合只存在一个值,因此调用了聚合函数之后,Null
值会被剔除掉。
select max(case when job='CLERK'
then ename else null end) as clerks,
max(case when job='ANALYST'
then ename else null end) as analysts,
max(case when job='MANAGER'
then ename else null end) as mgrs,
max(case when job='PRESIDENT'
then ename else null end) as prez,
max(case when job='SALESMAN'
then ename else null end) as sales
from (
select e.job,
e.ename,
(select count(*) from emp d
where e.job=d.job and e.empno < d.empno) as rnk
from emp e
) x
group by rnk
CLERKS ANALYSTS MGRS PREZ SALES
------ -------- ----- ---- ------
MILLER FORD CLARK KING TURNER
JAMES SCOTT BLAKE MARTIN
ADAMS JONES WARD
SMITH ALLEN