1.结果集分页1.1.只有做过了排序，才有可能准确地从结果集中返回指定区间的记录1.2.DB21.3.Oracle1.4.SQL Server1.5.sql

select sal  from (select row_number() over (order by sal) as rn,       sal  from emp       ) x where rn between 1 and 5SAL---- 800950110012501250

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 10SAL----- 1300 1500 1600 2450 2850

1.6.PostgreSQL1.7.MySQL1.8.sql

select sal  from emp order by sal limit 5 offset 0SAL------   800   950  1100  1250  1250

1.8.2.sql

 select sal  from emp order by sal limit 5 offset 5SAL----- 1300 1500 1600 2450 2850

2.跳过n行记录2.1.获得第一个员工、第三个员工，等等2.2.DB22.3.Oracle2.4.SQL Server2.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.PostgreSQL2.7.MySQL2.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.DB23.3.Oracle3.4.SQL Server3.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.PostgreSQL3.7.MySQL3.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.DB24.2.Oracle4.3.SQL Server4.4.窗口函数DENSE_RANK OVER

select dense_rank() over(order by sal) rnk, sal   from emp

4.5.PostgreSQL4.6.MySQL4.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.DB25.2.Oracle5.3.SQL Server5.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.PostgreSQL5.6.MySQL5.7.sql

select distinct job  from empselect job  from emp group by job

5.7.3.GROUP BY和DISTINCT是两个非常不同的子句，它们是不可互换的6.骑士值6.1.返回一个结果集，其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资6.2.DB26.3.SQL Server6.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.PostgreSQL6.7.MySQL6.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