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

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   SCOTTANALYST   FORDCLERK     SMITHCLERK     ADAMSCLERK     MILLERCLERK     JAMESMANAGER   JONESMANAGER   CLARKMANAGER   BLAKEPRESIDENT KINGSALESMAN  ALLENSALESMAN  MARTINSALESMAN  TURNERSALESMAN  WARD

3.2.结果集

CLERKS ANALYSTS MGRS  PREZ    SALES------ -------- ----- ---- ------ ---------------MILLER   FORD    CLARK      KING    TURNERJAMES    SCOTT   BLAKE              MARTINADAMS            JONES           WARDSMITH                               ALLEN

3.3.DB23.4.Oracle3.5.SQL Server3.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 BY3.7.PostgreSQL3.8.MySQL3.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 BY3.9.1.3.使用CASE表达式和聚合函数MAX实现结果集变换4.反向变换结果集成一列4.1.把一个查询结果合并成一列4.1.1.希望返回DEPTNO等于10的全体员工的ENAME、JOB和SAL，并且想把3列值合并成1列4.2.DB24.3.Oracle4.4.SQL Server4.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.DB25.3.SQL Server5.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 deptnoDEPTNO        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