极客教程SQL 计算当前记录和下一条记录之间的日期差，计算两个日期之间相差多少天（特别是当两者分别存储于不同的行的时候）。例如，对于 DEPTNO 等于 10 的部门的每一个员工，你希望计算出他们的入职日期之间相差多少天。
SQL 计算当前记录和下一条记录之间的日期差 问题描述
计算两个日期之间相差多少天（特别是当两者分别存储于不同的行的时候）。例如，对于 DEPTNO 等于 10 的部门的每一个员工，你希望计算出他们的入职日期之间相差多少天。
SQL 计算当前记录和下一条记录之间的日期差 解决方案
要解决本问题有一个诀窍是，在早于当前入职时间的所有记录里找出 HIREDATE 的最小值。如此一来，剩下的工作就是利用 8.2 节里提到的技巧来计算出两个日期之间相差多少天。
DB2
使用标量子查询找出相对于当前 HIREDATE 的下一个 HIREDATE。然后，调用 DAYS 函数计算两个日期之间相差多少天。
1 select x.*,
2 days(x.next_hd) - days(x.hiredate) diff
3 from (
4 select e.deptno, e.ename, e.hiredate,
5 (select min(d.hiredate) from emp d
6 where d.hiredate > e.hiredate) next_hd
7 from emp e
8 where e.deptno = 10
9 ) x
MySQL 和 SQL Server
使用标量子查询找出相对于当前 HIREDATE 的下一个 HIREDATE。然后，调用 DATEDIFF 函数计算两个日期之间相差多少天。下面的代码里以 SQL Server 版本的 DATEDIFF 函数为例。
1 select x.*,
2 datediff(day,x.hiredate,x.next_hd) diff
3 from (
4 select e.deptno, e.ename, e.hiredate,
5 (select min(d.hiredate) from emp d
6 where d.hiredate > e.hiredate) next_hd
7 from emp e
8 where e.deptno = 10
9 ) x
对于 MySQL 版本的 DATEDIFF 函数，我们需要省略第一个参数 day，并把剩下的两个参数的顺序颠倒过来。
2 datediff(x.next_hd, x.hiredate) diff
Oracle
对于 Oracle 8i 及后续版本，使用窗口函数 LEAD OVER 访问相对于当前 HIREDATE 的下一个 HIREDATE，然后执行减法运算。
1 select ename, hiredate, next_hd,
2 next_hd - hiredate diff
3 from (
4 select deptno, ename, hiredate,
5 lead(hiredate)over(order by hiredate) next_hd
6 from emp
7 )
8 where deptno=10
对于 Oracle 8i 及更早的版本，则需要采用下面的 PostgreSQL 解决方案。
PostgreSQL
使用标量子查询找出相对于当前 HIREDATE 的下一个 HIREDATE。然后，直接利用减法运算得出两者相差多少天。
1 select x.*,
2 x.next_hd - x.hiredate as diff
3 from (
4 select e.deptno, e.ename, e.hiredate,
5 (select min(d.hiredate) from emp d
6 where d.hiredate > e.hiredate) as next_hd
7 from emp e
8 where e.deptno = 10
9 ) x
SQL 计算当前记录和下一条记录之间的日期差 扩展知识
DB2、MySQL、PostgreSQL 和 SQL Server
除了语法上的差别，所有这些解决方案的做法是相同的：使用标量子查询找出相对于当前 HIREDATE 的下一个 HIREDATE，然后使用本章 8.2 节里用过的技巧计算出两个日期之间相差多少天。
Oracle
窗口函数 LEAD OVER 非常有用，它可以访问“未来”的行（“未来”是相对于当前行而言的，由 ORDER BY 子句决定）。不必使用额外的连接查询就可以访问当前行前后的行数据，这种能力有助于我们写出更高效、可读性更好的代码。当使用窗口函数时，要记住它们是在 WHERE 子句之后才被评估执行的，这就是为什么本解决方案需要一个内嵌视图。如果我们把过滤 DEPTNO 的动作移到内嵌视图里面的话，结果就不一样了（就变成了只考虑 DEPTNO 等于 10 的员工的 HIREDATE）。关于 Oracle 的 LEAD 和 LAG 函数，需要特别指出的一点是它们对于重复项的处理。在前言部分我提到过，本书的实例都不包含“防御性代码”，因为有太多无法预见的状况都可能导致代码无法正常执行。也就是说，即使我们能预见到每一种可能出现的问题，但最终写出的 SQL 却可能已经冗繁到不具有可读性。因此，在大多数情况下，一个解决方案的意义在于它提供了一种技巧：我们能将其用于线上系统，但是又必须事先做好测试，并针对具体的数据做出必要的调整。对于本例而言，有一种情况稍后需要简单讨论一下，因为针对它的变通方案不是那么显而易见，对于不熟悉 Oracle 的读者而言尤其如此。本例中 EMP 表里不存在重复的 HIREDATE，但是在一个表里出现重复的日期当然是可能的（并且非常可能）。考虑 DEPTNO 等于 10 的员工及其对应的 HIREDATE。
select ename, hiredate
from emp
where deptno=10
order by 2
ENAME HIREDATE
------ -----------
CLARK 09-JUN-1981
KING 17-NOV-1981
MILLER 23-JAN-1982
为了讨论的需要，我们插入 4 条重复数据，这样就有（包括 KING 在内的）5 个员工的 HIREDATE 都是 11 月 17 日了。
insert into emp (empno,ename,deptno,hiredate)
values (1,'ant',10,to_date('17-NOV-1981'))
insert into emp (empno,ename,deptno,hiredate)
values (2,'joe',10,to_date('17-NOV-1981'))
insert into emp (empno,ename,deptno,hiredate)
values (3,'jim',10,to_date('17-NOV-1981'))
insert into emp (empno,ename,deptno,hiredate)
values (4,'choi',10,to_date('17-NOV-1981'))
select ename, hiredate
from emp
where deptno=10
order by 2
ENAME HIREDATE
------ -----------
CLARK 09-JUN-1981
ant 17-NOV-1981
joe 17-NOV-1981
KING 17-NOV-1981
jim 17-NOV-1981
choi 17-NOV-1981
MILLER 23-JAN-1982
现在 DEPTNO 等于 10 的部门里就有不止一个人的 HIREDATE 是同一天了。如果仍然使用上述解决方案（但是要把 DEPTNO 过滤动作移到内嵌视图里面去，我们只关注 DEPTNO 等于 10 的员工及其 HIREDATE），返回的结果集就变成如下所示的输出内容。
select ename, hiredate, next_hd,
next_hd - hiredate diff
from (
select deptno, ename, hiredate,
lead(hiredate)over(order by hiredate) next_hd
from emp
where deptno=10
)
ENAME HIREDATE NEXT_HD DIFF
------ ----------- ----------- ----------
CLARK 09-JUN-1981 17-NOV-1981 161
ant 17-NOV-1981 17-NOV-1981 0
joe 17-NOV-1981 17-NOV-1981 0
KING 17-NOV-1981 17-NOV-1981 0
jim 17-NOV-1981 17-NOV-1981 0
choi 17-NOV-1981 23-JAN-1982 67
MILLER 23-JAN-1982 (null) (null)
看一下 HIREDATE 相同的 5 个员工，他们中有 4 个人的 DIFF 值是 0。这显然不正确。对于 HIREDATE 相同的员工而言，应该与下一个不同的 HIREDATE 相比较，例如 HIREDATE 是 11 月 17 日的员工要与 MILLER 的 HIREDATE 相比较。问题的根源在于 LEAD 函数仅仅按照 HIREDATE 排序，却不会自动去重。因此，如果把 ANT 的 HIREDATE 与 JOE 的相比较，相差的天数就是 0，如此一来 ANT 的 DIFF 值就变成 0 了。所幸 Oracle 已经为这种情况提供了一个简单的变通方案。当调用 LEAD 函数的时候，我们可以传递一个参数用于指定“未来行”的位置（例如，它位于下一行，或者隔了 10 行之后，等等）。因此，对于员工 ANT，需要跨过 5 行，而不是一行（我们希望跳过所有重复的 HIREDATE），去看一下 MILLER 的 HIREDATE。员工 JOE 距离 MILLER 有 4 行的距离，JIM 则是 3 行，KING 是 2 行，CHOI 幸运地只相隔一行。为了获取正确答案，只要把上述每一个员工所在行到 MILLER 的距离值作为参数传递给 LEAD 函数即可。该解决方案如下所示。
select ename, hiredate, next_hd,
next_hd - hiredate diff
from (
select deptno, ename, hiredate,
lead(hiredate,cnt-rn+1)over(order by hiredate) next_hd
from (
select deptno,ename,hiredate,
count(*)over(partition by hiredate) cnt,
row_number()over(partition by hiredate order by empno) rn
from emp
where deptno=10
)
)
ENAME HIREDATE NEXT_HD DIFF
------ ----------- ----------- ----------
CLARK 09-JUN-1981 17-NOV-1981 161
ant 17-NOV-1981 23-JAN-1982 67
joe 17-NOV-1981 23-JAN-1982 67
jim 17-NOV-1981 23-JAN-1982 67
choi 17-NOV-1981 23-JAN-1982 67
KING 17-NOV-1981 23-JAN-1982 67
MILLER 23-JAN-1982 (null) (null)
现在我们能得到正确的计算结果了。所有 HIREDATE 相同的员工都会和下一个不同的 HIREDATE 相比较，而不会匹配到一个相同的 HIREDATE 值。如果这个替代方案不是那么容易理解，不妨把上述查询分解开来看。先从内嵌视图开始。
select deptno,ename,hiredate,
count(*)over(partition by hiredate) cnt,
row_number()over(partition by hiredate order by empno) rn
from emp
where deptno=10
DEPTNO ENAME HIREDATE CNT RN
------ ------ ----------- ---------- ----------
10 CLARK 09-JUN-1981 1 1
10 ant 17-NOV-1981 5 1
10 joe 17-NOV-1981 5 2
10 jim 17-NOV-1981 5 3
10 choi 17-NOV-1981 5 4
10 KING 17-NOV-1981 5 5
10 MILLER 23-JAN-1982 1 1
窗口函数 COUNT OVER 计算每一种 HIREDATE 值出现的次数，并为每一行记录返回该值。对于那个重复的 HIREDATE，CNT 的值都是 5。窗口函数 ROW_NUMBER OVER 按照 EMPNO 为每一个员工排名。排名按照 HIREDATE 分区，除非有重复的 HIREDATE 出现，否则每个员工对应的 RN 值都是 1。现在，所有重复数据都被分组计数并算出其在分组里的排名，而该排名值可以用于度量当前 HIREDATE 到下一个 HIREDATE（MILLER 的 HIREDATE）的距离。调用 LEAD 函数时，我们通过从 CNT 里减去 RN 值并加 1 来得出该距离值。
select deptno, ename, hiredate,
cnt-rn+1 distance_to_miller,
lead(hiredate,cnt-rn+1)over(order by hiredate) next_hd
from (
select deptno,ename,hiredate,
count(*)over(partition by hiredate) cnt,
row_number()over(partition by hiredate order by empno) rn
from emp
where deptno=10
)
DEPTNO ENAME HIREDATE DISTANCE_TO_MILLER NEXT_HD
------ ------ ----------- ------------------ -----------
10 CLARK 09-JUN-1981 1 17-NOV-1981
10 ant 17-NOV-1981 5 23-JAN-1982
10 joe 17-NOV-1981 4 23-JAN-1982
10 jim 17-NOV-1981 3 23-JAN-1982
10 choi 17-NOV-1981 2 23-JAN-1982
10 KING 17-NOV-1981 1 23-JAN-1982
10 MILLER 23-JAN-1982 1 (null)
如上所示，通过传递适当的距离值以跳过若干行重复数据，LEAD 函数就能实现正确的日期比较了。