选读SQL经典实例笔记11_结果集变换
1.选读SQL经典实例笔记23_读后总结与感想兼导读2.选读SQL经典实例笔记01_检索和排序3.选读SQL经典实例笔记02_多表查询4.选读SQL经典实例笔记03_DML和元数据5.选读SQL经典实例笔记04_日期运算(上)6.选读SQL经典实例笔记05_日期运算(下)7.选读SQL经典实例笔记06_日期处理(上)8.选读SQL经典实例笔记07_日期处理(下)9.选读SQL经典实例笔记08_区间查询10.选读SQL经典实例笔记09_数值处理11.选读SQL经典实例笔记10_高级查询
12.选读SQL经典实例笔记11_结果集变换
13.选读SQL经典实例笔记12_桶、图和小计14.选读SQL经典实例笔记13_case与聚合15.选读SQL经典实例笔记14_层次查询16.选读SQL经典实例笔记15_窗口函数17.选读SQL经典实例笔记16_逻辑否定18.选读SQL经典实例笔记17_最多和最少19.选读SQL经典实例笔记18_Exactly20.选读SQL经典实例笔记19_Any和All21.选读SQL经典实例笔记20_Oracle语法示例22.选读SQL经典实例笔记21_字符串处理23.选读SQL经典实例笔记22_2版增补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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业