选读SQL经典实例笔记12_桶、图和小计
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. 数据放入若干个大小固定的桶(bucket)里,每个桶的元素个数是事先定好的
1.1.1. 针对商值向上取整
1.2. DB2
1.3. Oracle
1.4. SQL Server
1.5. 使用窗口函数ROW_NUMBER OVER
1.5.1. sql
select ceil(row_number()over(order by empno)/5.0) grp,
empno,
ename
from emp
1.6. PostgreSQL
1.7. MySQL
1.8. 使用标量子查询为每个EMPNO生成一个序号
1.8.1. sql
select ceil(rnk/5.0) as grp,
empno, ename
from (
select e.empno, e.ename,
(select count(*) from emp d
where e.empno < d.empno)+1 as rnk
from emp e
) x
order by grp
2. 创建预定数目的桶
2.1. 不在乎每个桶里有多少个元素,但需要创建固定数目(数目已知)的桶
2.2. DB2
2.2.1. sql
select mod(row_number()over(order by empno),4)+1 grp,
empno,
ename
from emp
order by 1
2.3. Oracle
2.4. SQL Server
2.5. 使用窗口函数NTILE
2.5.1. sql
select ntile(4)over(order by empno) grp,
empno,
ename
from emp
2.6. PostgreSQL
2.7. MySQL
2.8. 使用自连接基于EMPNO为每一行生成一个序号
2.8.1. sql
select mod(count(*),4)+1 as grp,
e.empno,
e.ename
from emp e, emp d
where e.empno >= d.empno
group by e.empno,e.ename
order by 1
3. 创建水平直方图
3.1. 结果集
3.1.1. sql
DEPTNO CNT
------ ----------
10 ****
20 ********
30 ****
3.2. DB2
3.2.1. sql
select deptno,
repeat('*',count(*)) cnt
from emp
group by deptno
3.3. Oracle
3.4. PostgreSQL
3.5. MySQL
3.6. 使用LPAD函数生成所需的字符串“*”
3.6.1. sql
select deptno,
lpad('*',count(*),'*') as cnt
from emp
group by deptno
3.6.2. sql
select deptno,
lpad('*',count(*)::integer,'*') as cnt
from emp
group by deptno
3.6.2.1. CAST函数调用是必须的,因为PostgreSQL 要求LPAD的参数为整数
3.7. SQL Server
3.7.1. sql
select deptno,
replicate('*',count(*)) cnt
from emp
group by deptno
4. 创建垂直直方图
4.1. 结果集
4.1.1. sql
D10 D20 D30
--- --- ---
*
* *
* *
* * *
* * *
* *
4.2. DB2
4.3. Oracle
4.4. SQL Server
4.5. 使用窗口函数ROW_NUMBER OVER
4.5.1. sql
select max(deptno_10) d10,
max(deptno_20) d20,
max(deptno_30) d30
from (
select row_number()over(partition by deptno order by empno) rn,
case when deptno=10 then '*' else null end deptno_10,
case when deptno=20 then '*' else null end deptno_20,
case when deptno=30 then '*' else null end deptno_30
from emp
) x
group by rn
order by 1 desc, 2 desc, 3 desc
4.6. PostgreSQL
4.7. MySQL
4.8. 使用标量子查询
4.8.1. sql
select max(deptno_10) as d10,
max(deptno_20) as d20,
max(deptno_30) as d30
from (
select case when e.deptno=10 then '*' else null end deptno_10,
case when e.deptno=20 then '*' else null end deptno_20,
case when e.deptno=30 then '*' else null end deptno_30,
(select count(*) from emp d
where e.deptno=d.deptno and e.empno < d.empno ) as rnk
from emp e
) x
group by rnk
order by 1 desc, 2 desc, 3 desc
5. 返回非分组列
5.1. 希望找出每个部门工资最高和最低的员工,同时也希望找出每个职位对应的工资最高和最低的员工
5.2. DB2
5.3. Oracle
5.4. SQL Server
5.5. 窗口函数MAX OVER和MIN OVER
5.5.1. sql
select deptno,ename,job,sal,
case when sal = max_by_dept
then 'TOP SAL IN DEPT'
when sal = min_by_dept
then 'LOW SAL IN DEPT'
end dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end job_status
from (
select deptno,ename,job,sal,
max(sal)over(partition by deptno) max_by_dept,
max(sal)over(partition by job) max_by_job,
min(sal)over(partition by deptno) min_by_dept,
min(sal)over(partition by job) min_by_job
from emp
) emp_sals
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
5.6. PostgreSQL
5.7. MySQL
5.8. 使用标量子查询
5.8.1. sql
select deptno,ename,job,sal,
case when sal = max_by_dept
then 'TOP SAL IN DEPT'
when sal = min_by_dept
then 'LOW SAL IN DEPT'
end as dept_status,
case when sal = max_by_job
then 'TOP SAL IN JOB'
when sal = min_by_job
then 'LOW SAL IN JOB'
end as job_status
from (
select e.deptno,e.ename,e.job,e.sal,
(select max(sal) from emp d
where d.deptno = e.deptno) as max_by_dept,
(select max(sal) from emp d
where d.job = e.job) as max_by_job,
(select min(sal) from emp d
where d.deptno = e.deptno) as min_by_dept,
(select min(sal) from emp d
where d.job = e.job) as min_by_job
from emp e
) x
where sal in (max_by_dept,max_by_job,
min_by_dept,min_by_job)
6. 简单的小计
6.1. 结果集
6.1.1. sql
JOB SAL
--------- ----------
ANALYST 6000
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
TOTAL 29025
6.2. DB2
6.3. Oracle
6.4. 使用GROUP BY的ROLLUP
6.4.1. sql
select case grouping(job)
when 0 then job
else 'TOTAL'
end job,
sum(sal) sal
from emp
group by rollup(job)
6.5. PostgreSQL
6.5.1. sql
select job, sum(sal) as sal
from emp
group by job
union all
select 'TOTAL', sum(sal)
from emp
6.6. MySQL
6.7. SQL Server
6.8. 使用WITH ROLLUP构造
6.8.1. sql
select coalesce(job,'TOTAL') job,
sum(sal) sal
from emp
group by job with rollup
7. 所有可能的表达式组合的小计
7.1. 结果集
7.2. DB2
7.2.1. sql
select deptno,
job,
case cast(grouping(deptno) as char(1))||
cast(grouping(job) as char(1))
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'TOTAL FOR TABLE'
end category,
sum(sal)
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
7.3. Oracle
7.3.1. sql
select deptno,
job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by cube(deptno,job)
order by grouping(job),grouping(deptno)
7.4. PostgreSQL
7.5. MySQL
7.6. UNION ALL
7.6.1. sql
select deptno, job,
'TOTAL BY DEPT AND JOB' as category,
sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
elect deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
elect null,null,'GRAND TOTAL FOR TABLE', sum(sal)
from emp
7.7. SQL Server
7.7.1. sql
select deptno,
job,
case cast(grouping(deptno)as char(1))+
cast(grouping(job)as char(1))
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by deptno,job with cube
order by grouping(job),grouping(deptno)
合集:
选读SQL经典实例
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业