SQL 查找骑士值|极客教程

SQL 查找骑士值，你想返回一个结果集，其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资。你希望返回如下所示的结果集。

SQL 查找骑士值问题描述

你想返回一个结果集，其中包括每个员工的姓名、部门、工资、入职时间以及每一个部门里最近入职的那个员工的工资。你希望返回如下所示的结果集。

DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       1300
    10 KING             5000 17-NOV-1981       1300
    10 CLARK            2450 09-JUN-1981       1300
    20 ADAMS            1100 12-JAN-1983       1100
    20 SCOTT            3000 09-DEC-1982       1100
    20 FORD             3000 03-DEC-1981       1100
    20 JONES            2975 02-APR-1981       1100
    20 SMITH             800 17-DEC-1980       1100
    30 JAMES             950 03-DEC-1981        950
    30 MARTIN           1250 28-SEP-1981        950
    30 TURNER           1500 08-SEP-1981        950
    30 BLAKE            2850 01-MAY-1981        950
    30 WARD             1250 22-FEB-1981        950
    30 ALLEN            1600 20-FEB-1981        950

我把 LATEST_SAL 值称作“骑士值”（knight value），因为找出它们的方法类似于国际象棋游戏中骑士的路径。我们找到结果的方式就像一个骑士确定新位置：跳到一行，然后转向并跳到一个不同的列（图 11-1）。为找到正确的 LATEST_SAL 值，你必须先定位（跳到）每个 DEPTNO 对应的最新 HIREDATE，然后选择（跳到）那一行的 SAL 列。

图 11-1：骑士值来源于国际象棋中骑士的走法

　“骑士值”是由我的一个非常聪明的同事 Kay Young 创造出来的。在他帮我做完正确性检查后，我告诉他我被难住了，因为无法为本实例想出一个恰到好处的标题。考虑到需要先对当前行数据做出评估，然后“跳”到另外一行并取出某列的值，于是他提出了“骑士值”这个名词。

SQL 查找骑士值解决方案

DB2 和 SQL Server
在子查询中使用 CASE 表达式，并返回每个 DEPTNO 对应的最近入职的那个员工的 SAL；对于其他工资值，则返回 0。在外层的查询中使用窗口函数 MAX OVER 为每个员工的部门返回非零的 SAL。

 1 select deptno,
 2        ename,
 3        sal,
 4        hiredate,
 5        max(latest_sal)over(partition by deptno) latest_sal
 6   from (
 7 select deptno,
 8        ename,
 9        sal,
10        hiredate,
11        case
12          when hiredate = max(hiredate)over(partition by deptno)
13          then sal else 0
14        end latest_sal
15   from emp
16        ) x
17  order by 1, 4 desc

MySQL 和 PostgreSQL
使用两层嵌套的标量子查询。首先，找出每个 DEPTO 对应的最近入职的员工的 HIREDATE。然后使用聚合函数 MAX（如果有重复项）找出每个 DEPTNO 对应的最近入职的员工的 SAL。

 1  select e.deptno,
 2         e.ename,
 3         e.sal,
 4         e.hiredate,
 5         (select max(d.sal)
 6            from emp d
 7           where d.deptno  = e.deptno
 8             and d.hiredate =
 9                 (select max(f.hiredate)
10                    from emp f
11                   where f.deptno = e.deptno)) as latest_sal
12    from emp e
13   order by 1, 4 desc

Oracle
使用窗口函数 MAX OVER 返回每个 DEPTNO 对应的最高 SAL 值。在 KEEP 子句中使用函数 DENSE_RANK 和 LAST，并按照 HIREDATE 排序，为给定 DEPTNO 对应的最新 HIREDATE 返回最高的 SAL 值。

1  select deptno,
2         ename,
3         sal,
4         hiredate,
5         max(sal)
6           keep(dense_rank last order by hiredate)
7           over(partition by deptno) latest_sal
8    from emp
9   order by 1, 4 desc

SQL 查找骑士值扩展知识

DB2 和 SQL Server
首先在 CASE 表达式中使用窗口函数 MAX OVER 找出每个 DEPTNO 对应的最近入职的员工。如果员工的 HIREDATE 等于 MAX OVER 的返回值，那么 CASE 表达式就会返回该员工的 SAL 值；否则，返回 0。这一步的结果如下所示。

select deptno,
      ename,
      sal,
      hiredate,
      case
         when hiredate = max(hiredate)over(partition by deptno)
         then sal else 0
      end latest_sal
  from emp
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 CLARK            2450 09-JUN-1981          0
    10 KING             5000 17-NOV-1981          0
    10 MILLER           1300 23-JAN-1982       1300
    20 SMITH             800 17-DEC-1980          0
    20 ADAMS            1100 12-JAN-1983       1100
    20 FORD             3000 03-DEC-1981          0
    20 SCOTT            3000 09-DEC-1982          0
    20 JONES            2975 02-APR-1981          0
    30 ALLEN            1600 20-FEB-1981          0
    30 BLAKE            2850 01-MAY-1981          0
    30 MARTIN           1250 28-SEP-1981          0
    30 JAMES             950 03-DEC-1981        950
    30 TURNER           1500 08-SEP-1981          0
    30 WARD             1250 22-FEB-1981          0

LATEST_SAL 值要么是 0，要么是最近入职的员工的 SAL，因此我们可以把上述查询作为一个内嵌视图，并在此基础上再次使用 MAX OVER，这次我们要为每个 DEPTNO 返回最大的非零 LATEST_SAL。

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
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       1300
    10 KING             5000 17-NOV-1981       1300
    10 CLARK            2450 09-JUN-1981       1300
    20 ADAMS            1100 12-JAN-1983       1100
    20 SCOTT            3000 09-DEC-1982       1100
    20 FORD             3000 03-DEC-1981       1100
    20 JONES            2975 02-APR-1981       1100
    20 SMITH             800 17-DEC-1980       1100
    30 JAMES             950 03-DEC-1981        950
    30 MARTIN           1250 28-SEP-1981        950
    30 TURNER           1500 08-SEP-1981        950
    30 BLAKE            2850 01-MAY-1981        950
    30 WARD             1250 22-FEB-1981        950
    30 ALLEN            1600 20-FEB-1981        950

MySQL 和 PostgreSQL
首先，使用标量子查询找出每个 DEPTNO 对应的最新入职的员工的 HIREDATE。

select e.deptno,
       e.ename,
       e.sal,
       e.hiredate,
       (select max(f.hiredate)
          from emp f
         where f.deptno = e.deptno) as last_hire
  from emp e
 order by 1, 4 desc
　
DEPTNO ENAME             SAL HIREDATE      LAST_HIRE
------ ---------- ---------- ----------- -----------
    10 MILLER           1300 23-JAN-1982 23-JAN-1982
    10 KING             5000 17-NOV-1981 23-JAN-1982
    10 CLARK            2450 09-JUN-1981 23-JAN-1982
    20 ADAMS            1100 12-JAN-1983 12-JAN-1983
    20 SCOTT            3000 09-DEC-1982 12-JAN-1983
    20 FORD             3000 03-DEC-1981 12-JAN-1983
    20 JONES            2975 02-APR-1981 12-JAN-1983
    20 SMITH             800 17-DEC-1980 12-JAN-1983
    30 JAMES             950 03-DEC-1981 03-DEC-1981
    30 MARTIN           1250 28-SEP-1981 03-DEC-1981
    30 TURNER           1500 08-SEP-1981 03-DEC-1981
    30 BLAKE            2850 01-MAY-1981 03-DEC-1981
    30 WARD             1250 22-FEB-1981 03-DEC-1981
    30 ALLEN            1600 20-FEB-1981 03-DEC-1981

然后，找出每个 DEPTNO 对应的入职日期等于 LAST_HIRE 的员工的 SAL。使用聚合函数 MAX 找出最大值（如果同一天入职的员工不止一人的话）。

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
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       1300
    10 KING             5000 17-NOV-1981       1300
    10 CLARK            2450 09-JUN-1981       1300
    20 ADAMS            1100 12-JAN-1983       1100
    20 SCOTT            3000 09-DEC-1982       1100
    20 FORD             3000 03-DEC-1981       1100
    20 JONES            2975 02-APR-1981       1100
    20 SMITH             800 17-DEC-1980       1100
    30 JAMES             950 03-DEC-1981        950
    30 MARTIN           1250 28-SEP-1981        950
    30 TURNER           1500 08-SEP-1981        950
    30 BLAKE            2850 01-MAY-1981        950
    30 WARD             1250 22-FEB-1981        950
    30 ALLEN            1600 20-FEB-1981        950

Oracle
Oracle 8i 用户不妨采用前面的 DB2 解决方案。Oracle 9i 及后续版本则可以使用下面给出的解决方案。如下 Oracle 解决方案的关键在于利用 KEEP 子句。KEEP 子句为分组或者分区之后的行数据进行排序，并取出每组的第一行或最后一行。试想一下，去掉了 KEEP 子句的解决方案会如何。

select deptno,
       ename,
       sal,
       hiredate,
       max(sal) over(partition by deptno) latest_sal
  from emp
 order by 1, 4 desc
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       5000
    10 KING             5000 17-NOV-1981       5000
    10 CLARK            2450 09-JUN-1981       5000
    20 ADAMS            1100 12-JAN-1983       3000
    20 SCOTT            3000 09-DEC-1982       3000
    20 FORD             3000 03-DEC-1981       3000
    20 JONES            2975 02-APR-1981       3000
    20 SMITH             800 17-DEC-1980       3000
    30 JAMES             950 03-DEC-1981       2850
    30 MARTIN           1250 28-SEP-1981       2850
    30 TURNER           1500 08-SEP-1981       2850
    30 BLAKE            2850 01-MAY-1981       2850
    30 WARD             1250 22-FEB-1981       2850
    30 ALLEN            1600 20-FEB-1981       2850

去掉了 KEEP 子句的 MAX OVER 只会简单地返回每个 DEPTNO 对应的最高工资值，而不是最近入职的那个员工的 SAL。这里的 KEEP 子句通过指定 ORDER BY HIREDATE 按照 HIREDATE 为每个 DEPTNO 对应的工资值进行排序。然后，DENSE_RANK 函数按照升序为 HIREDATE 排序。最后，函数 LAST 决定针对哪一行记录使用聚合函数：基于 DENSE_RANK 排序最后的一行。对本例而言，聚合函数 MAX 针对的是最后一行 HIREDATE 所对应的 SAL 列。其实就是为了保留每个 DEPTNO 对应的 HIREDATE 排序最后的那个 SAL 值。
我们基于某一列（HIREDATE）为每个 DEPTNO 对应的行做排序，却针对另一列（SAL）做聚合计算（MAX）。Oracle 具备的这种先针对某一个维度做排序，继而又针对另一个维度做聚合计算的能力非常有用，其他数据库需要额外的连接查询和内嵌视图才能达到同样的效果。最后，通过在 KEEP 子句后面跟一个 OVER 子句，我们就能返回由 KEEP 子句为每一行“保留”下来的 SAL 值。
另外，我们还可以对 HIREDATE 实行降序排列，并保留第一个 SAL 值。比较下面的两个查询，它们都返回相同的结果集。

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
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       1300
    10 KING             5000 17-NOV-1981       1300
    10 CLARK            2450 09-JUN-1981       1300
    20 ADAMS            1100 12-JAN-1983       1100
    20 SCOTT            3000 09-DEC-1982       1100
    20 FORD             3000 03-DEC-1981       1100
    20 JONES            2975 02-APR-1981       1100
    20 SMITH             800 17-DEC-1980       1100
    30 JAMES             950 03-DEC-1981        950
    30 MARTIN           1250 28-SEP-1981        950
    30 TURNER           1500 08-SEP-1981        950
    30 BLAKE            2850 01-MAY-1981        950
    30 WARD             1250 22-FEB-1981        950
    30 ALLEN            1600 20-FEB-1981        950
　
　
select deptno,
       ename,
       sal,
       hiredate,
       max(sal)
         keep(dense_rank first order by hiredate desc)
         over(partition by deptno) latest_sal
  from emp
 order by 1, 4 desc
　
DEPTNO ENAME             SAL HIREDATE    LATEST_SAL
------ ---------- ---------- ----------- ----------
    10 MILLER           1300 23-JAN-1982       1300
    10 KING             5000 17-NOV-1981       1300
    10 CLARK            2450 09-JUN-1981       1300
    20 ADAMS            1100 12-JAN-1983       1100
    20 SCOTT            3000 09-DEC-1982       1100
    20 FORD             3000 03-DEC-1981       1100
    20 JONES            2975 02-APR-1981       1100
    20 SMITH             800 17-DEC-1980       1100
    30 JAMES             950 03-DEC-1981        950
    30 MARTIN           1250 28-SEP-1981        950
    30 TURNER           1500 08-SEP-1981        950
    30 BLAKE            2850 01-MAY-1981        950
    30 WARD             1250 22-FEB-1981        950
    30 ALLEN            1600 20-FEB-1981        950