极客教程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