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