选读SQL经典实例笔记11_结果集变换.png

1. 变换结果集成一行

1.1. 结果集

DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

1.2. 结果集

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

1.3. sql

select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
        sum(case when deptno=20 then 1 else 0 end) as deptno_20,
        sum(case when deptno=30 then 1 else 0 end) as deptno_30
 from emp

1.3.1. 对于每一行的原始数据，使用CASE表达式把行变换成列

1.4. sql

select max(case when deptno=10 then empcount else null end) as deptno_10,
       max(case when deptno=20 then empcount else null end) as deptno_20,
       max(case when deptno=30 then empcount else null end) as deptno_30
  from (
select deptno, count(*) as empcount
  from emp
 group by deptno
       ) x

1.4.1. 用内嵌视图生成每个部门的员工总人数

1.4.2. 主查询里的CASE表达式把行转换成列

1.4.3. 调用MAX函数把几列合并为一行

2. 反向变换结果集

2.1. 结果集

DEPTNO_10  DEPTNO_20  DEPTNO_30
--------- ---------- ----------
        3          5          6

2.2. 结果集

DEPTNO        CNT
------ ----------
    10          3
    20          5
    30          6

2.3. sql

select dept.deptno,
         case dept.deptno
              when 10 then emp_cnts.deptno_10
              when 20 then emp_cnts.deptno_20
              when 30 then emp_cnts.deptno_30
         end as CNT
    from (
  select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
         sum(case when deptno=20 then 1 else 0 end) as deptno_20,
         sum(case when deptno=30 then 1 else 0 end) as deptno_30
    from emp
         ) emp_cnts,
         (select deptno from dept where deptno ＜= 30) dept

3. 变换结果集成多行

3.1. 结果集

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

3.2. 结果集

CLERKS ANALYSTS MGRS  PREZ    SALES
------ -------- ----- ---- ------ ---------------
MILLER   FORD    CLARK      KING    TURNER
JAMES    SCOTT   BLAKE              MARTIN
ADAMS            JONES           WARD
SMITH                               ALLEN

3.3. DB2

3.4. Oracle

3.5. SQL Server

3.6. 使用窗口函数ROW_NUMBER OVER确保每一个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 (
  select job,
         ename,
         row_number()over(partition by job order by ename) rn
    from emp
         ) x
   group by rn

3.6.1.1. 为了剔除掉Null，需要调用聚合函数MAX，并基于RN执行GROUP BY

3.7. PostgreSQL

3.8. MySQL

3.9. sql

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

3.9.1.1. 使用标量子查询基于EMPNO为每个员工排序

3.9.1.2. 针对标量子查询的返回值执行GROUP BY

3.9.1.3. 使用CASE表达式和聚合函数MAX实现结果集变换

4. 反向变换结果集成一列

4.1. 把一个查询结果合并成一列

4.1.1. 希望返回DEPTNO等于10的全体员工的ENAME、JOB和SAL，并且想把3列值合并成1列

4.2. DB2

4.3. Oracle

4.4. SQL Server

4.5. 使用窗口函数ROW_NUMBER OVER

select case rn
              when 1 then ename
              when 2 then job
              when 3 then cast(sal as char(4))
         end emps
    from (
  select e.ename,e.job,e.sal,
         row_number()over(partition by e.empno
                              order by e.empno) rn
    from emp e,
         (select *
            from emp where job='CLERK') four_rows
   where e.deptno=10
         ) x

5. 删除重复数据

5.1. 结果集

DEPTNO ENAME
------ ---------
    10 CLARK
       KING
       MILLER
    20 SMITH
       ADAMS
       FORD
       SCOTT
       JONES
    30 ALLEN
       BLAKE
       MARTIN
       JAMES
       TURNER
       WARD

5.1.1. 每个DEPTNO只显示一次

5.2. DB2

5.3. SQL Server

5.4. 使用窗口函数MIN OVER

select case when empno=min_empno
              then deptno else null
         end deptno,
         ename
    from (
  select deptno,
         min(empno)over(partition by deptno) min_empno,
         empno,
         ename
    from emp
         ) x

5.5. Oracle

select to_number(
           decode(lag(deptno)over(order by deptno),
                 deptno,null,deptno)
        ) deptno, ename
   from emp

6. 变换结果集以实现跨行计算

select deptno, sum(sal) as sal
  from emp
 group by deptno
DEPTNO        SAL
------ ----------
    10       8750
    20      10875
    30       9400

6.2. 算出上述DEPTNO 20和DEPTNO 10之间的工资总额的差值，以及上述DEPTNO 20和DEPTNO 30之间的工资总额差值

  select d20_sal - d10_sal as d20_10_diff,
         d20_sal - d30_sal as d20_30_diff
    from (
  select sum(case when deptno=10 then sal end) as d10_sal,
         sum(case when deptno=20 then sal end) as d20_sal,
         sum(case when deptno=30 then sal end) as d30_sal
    from emp
         ) totals_by_dept