|
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
复制代码 来源:https://www.cnblogs.com/lying7/p/17565552.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?立即注册
x
|