 # 1. 變換結果集成一行 ## 1.1. 結果集 ```sql DEPTNO CNT 10 3 20 5 30 ...
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
