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

1. 变换结果集成一行

1.1.  结果集

1. DEPTNO        CNT
2. ------ ----------
3.     10          3
4.     20          5
5.     30          6

复制代码

1.2. 结果集

1. DEPTNO_10  DEPTNO_20  DEPTNO_30
2. --------- ---------- ----------
3.         3          5          6

复制代码

1.3.  sql

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

复制代码

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

1.4. sql

1. select max(case when deptno=10 then empcount else null end) as deptno_10,
2.        max(case when deptno=20 then empcount else null end) as deptno_20,
3.        max(case when deptno=30 then empcount else null end) as deptno_30
4.   from (
5. select deptno, count(*) as empcount
6.   from emp
7. group by deptno
8.        ) x

复制代码

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

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

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

2. 反向变换结果集

2.1. 结果集

1. DEPTNO_10  DEPTNO_20  DEPTNO_30
2. --------- ---------- ----------
3.         3          5          6

复制代码

2.2. 结果集

1. DEPTNO        CNT
2. ------ ----------
3.     10          3
4.     20          5
5.     30          6

复制代码

2.3.   sql

1. select dept.deptno,
2.          case dept.deptno
3.               when 10 then emp_cnts.deptno_10
4.               when 20 then emp_cnts.deptno_20
5.               when 30 then emp_cnts.deptno_30
6.          end as CNT
7.     from (
8.   select sum(case when deptno=10 then 1 else 0 end) as deptno_10,
9.          sum(case when deptno=20 then 1 else 0 end) as deptno_20,
10.          sum(case when deptno=30 then 1 else 0 end) as deptno_30
11.     from emp
12.          ) emp_cnts,
13.          (select deptno from dept where deptno ＜= 30) dept

复制代码

3. 变换结果集成多行

3.1. 结果集

1. JOB       ENAME
2. --------- ----------
3. ANALYST   SCOTT
4. ANALYST   FORD
5. CLERK     SMITH
6. CLERK     ADAMS
7. CLERK     MILLER
8. CLERK     JAMES
9. MANAGER   JONES
10. MANAGER   CLARK
11. MANAGER   BLAKE
12. PRESIDENT KING
13. SALESMAN  ALLEN
14. SALESMAN  MARTIN
15. SALESMAN  TURNER
16. SALESMAN  WARD

复制代码

3.2. 结果集

1. CLERKS ANALYSTS MGRS  PREZ    SALES
2. ------ -------- ----- ---- ------ ---------------
3. MILLER   FORD    CLARK      KING    TURNER
4. JAMES    SCOTT   BLAKE              MARTIN
5. ADAMS            JONES           WARD
6. SMITH                               ALLEN

复制代码

3.3. DB2

3.4. Oracle

3.5. SQL Server

3.6. 使用窗口函数ROW_NUMBER OVER确保每一个JOB/ENAME组合都是唯一的

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

复制代码

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

3.7. PostgreSQL

3.8. MySQL

3.9. sql

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

复制代码

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

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

复制代码

5. 删除重复数据

5.1. 结果集

1. DEPTNO ENAME
2. ------ ---------
3.     10 CLARK
4.        KING
5.        MILLER
6.     20 SMITH
7.        ADAMS
8.        FORD
9.        SCOTT
10.        JONES
11.     30 ALLEN
12.        BLAKE
13.        MARTIN
14.        JAMES
15.        TURNER
16.        WARD

复制代码

5.1.1. 每个DEPTNO只显示一次

5.2. DB2

5.3. SQL Server

5.4. 使用窗口函数MIN OVER

1. select case when empno=min_empno
2.               then deptno else null
3.          end deptno,
4.          ename
5.     from (
6.   select deptno,
7.          min(empno)over(partition by deptno) min_empno,
8.          empno,
9.          ename
10.     from emp
11.          ) x

复制代码

5.5. Oracle

1. select to_number(
2.            decode(lag(deptno)over(order by deptno),
3.                  deptno,null,deptno)
4.         ) deptno, ename
5.    from emp

复制代码

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

1. select deptno, sum(sal) as sal
2.   from emp
3. group by deptno
4. DEPTNO        SAL
5. ------ ----------
6.     10       8750
7.     20      10875
8.     30       9400

复制代码

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

1.   select d20_sal - d10_sal as d20_10_diff,
2.         d20_sal - d30_sal as d20_30_diff
3.    from (
4.  select sum(case when deptno=10 then sal end) as d10_sal,
5.         sum(case when deptno=20 then sal end) as d20_sal,
6.         sum(case when deptno=30 then sal end) as d30_sal
7.    from emp
8.         ) totals_by_dept

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！