选读SQL经典实例笔记10_高级查询

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