选读SQL经典实例笔记10_高级查询
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. 只有做过了排序,才有可能准确地从结果集中返回指定区间的记录
1.2. DB2
1.3. Oracle
1.4. SQL Server
1.5. sql
select sal
from (
select row_number() over (order by sal) as rn,
sal
from emp
) x
where rn between 1 and 5
SAL
----
800
950
1100
1250
1250
1.5.2. sql
select sal
from (
select row_number() over (order by sal) as rn,
sal
from emp
) x
where rn between 6 and 10
SAL
-----
1300
1500
1600
2450
2850
1.6. PostgreSQL
1.7. MySQL
1.8. sql
select sal
from emp
order by sal limit 5 offset 0
SAL
------
800
950
1100
1250
1250
1.8.2. sql
select sal
from emp
order by sal limit 5 offset 5
SAL
-----
1300
1500
1600
2450
2850
2. 跳过n行记录
2.1. 获得第一个员工、第三个员工,等等
2.2. DB2
2.3. Oracle
2.4. SQL Server
2.5. 使用窗口函数ROW_NUMBER OVER为每一行分配一个序号
select ename
from (
select row_number() over (order by ename) rn,
ename
from emp
) x
where mod(rn,2) = 1
2.6. PostgreSQL
2.7. MySQL
2.8. 使用标量子查询
select x.ename
from (
select a.ename,
(select count(*)
from emp b
where b.ename <= a.ename) as rn
from emp a
)x
where mod(x.rn,2) = 1
3. 提取最靠前的n行记录
3.1. 基于某种排序方式从结果集中提取出限定数目的记录
3.2. DB2
3.3. Oracle
3.4. SQL Server
3.5. DENSE_RANK函数
select ename,sal
from (
select ename, sal,
dense_rank() over (order by sal desc) dr
from emp
) x
where dr <= 5
3.6. PostgreSQL
3.7. MySQL
3.8. 使用标量子查询
select ename,sal
from (
select (select count(distinct b.sal)
from emp b
where a.sal <= b.sal) as rnk,
a.sal,
a.ename
from emp a
)
where rnk <= 5
4. 对结果排序
4.1. DB2
4.2. Oracle
4.3. SQL Server
4.4. 窗口函数DENSE_RANK OVER
select dense_rank() over(order by sal) rnk, sal
from emp
4.5. PostgreSQL
4.6. MySQL
4.7. 标量子查询
select (select count(distinct b.sal)
from emp b
where b.sal <= a.sal) as rnk,
a.sal
from emp a
5. 删除重复项
5.1. DB2
5.2. Oracle
5.3. SQL Server
5.4. 窗口函数ROW_NUMBER OVER
select job
from (
select job,
row_number()over(partition by job order by job) rn
from emp
)x
where rn = 1
5.5. PostgreSQL
5.6. MySQL
5.7. sql
select distinct job
from emp
select job
from emp
group by job
5.7.3. GROUP BY和DISTINCT是两个非常不同的子句,它们是不可互换的
6. 骑士值
6.1. 返回一个结果集,其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资
6.2. DB2
6.3. SQL Server
6.4. 窗口函数MAX OVER
select deptno,
ename,
sal,
hiredate,
max(latest_sal)over(partition by deptno) latest_sal
from (
select deptno,
ename,
sal,
hiredate,
case
when hiredate = max(hiredate)over(partition by deptno)
then sal else 0
end latest_sal
from emp
) x
order by 1, 4 desc
6.5. Oracle
select deptno,
ename,
sal,
hiredate,
max(sal)
keep(dense_rank last order by hiredate)
over(partition by deptno) latest_sal
from emp
order by 1, 4 desc
6.6. PostgreSQL
6.7. MySQL
6.8. 两层嵌套的标量子查询
select e.deptno,
e.ename,
e.sal,
e.hiredate,
(select max(d.sal)
from emp d
where d.deptno = e.deptno
and d.hiredate =
(select max(f.hiredate)
from emp f
where f.deptno = e.deptno)) as latest_sal
from emp e
order by 1, 4 desc
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业