当前位置：极客教程 > SQL > SQL 实例 > SQL 计算同一组或分区的行之间的差

SQL 计算同一组或分区的行之间的差

SQL 计算同一组或分区的行之间的差，你想返回每个员工的 DEPTNO、ENAME 和 SAL，以及同一个部门（即 DEPTNO 相同）里不同员工之间的工资差距。工资差距指的是当前员工的 SAL 和入职日期紧随其后的那个员工的 SAL 之间的差值（其实你希望以部门为单位考察资历和工资是否存在相关性）。对于一个部门里入职日期最晚的那个员工，将其工资差距设置为 N/A。

SQL 计算同一组或分区的行之间的差 问题描述

你想返回每个员工的 DEPTNO、ENAME 和 SAL，以及同一个部门（即 DEPTNO 相同）里不同员工之间的工资差距。工资差距指的是当前员工的 SAL 和入职日期紧随其后的那个员工的 SAL 之间的差值（其实你希望以部门为单位考察资历和工资是否存在相关性）。对于一个部门里入职日期最晚的那个员工，将其工资差距设置为 N/A。最终结果集应该如下所示。

DEPTNO ENAME             SAL HIREDATE    DIFF
------ ---------- ---------- ----------- ----------
    10 CLARK            2450 09-JUN-1981      -2550
    10 KING             5000 17-NOV-1981       3700
    10 MILLER           1300 23-JAN-1982        N/A
    20 SMITH             800 17-DEC-1980      -2175
    20 JONES            2975 02-APR-1981        -25
    20 FORD             3000 03-DEC-1981          0
    20 SCOTT            3000 09-DEC-1982       1900
    20 ADAMS            1100 12-JAN-1983        N/A
    30 ALLEN            1600 20-FEB-1981        350
    30 WARD             1250 22-FEB-1981      -1600
    30 BLAKE            2850 01-MAY-1981       1350
    30 TURNER           1500 08-SEP-1981        250
    30 MARTIN           1250 28-SEP-1981        300
    30 JAMES             950 03-DEC-1981        N/A

SQL 计算同一组或分区的行之间的差 解决方案

这是说明 Oracle 窗口函数 LEAD OVER 和 LAG OVER 的便利性的另一个例子。不需要做额外的连接查询，我们就能方便地查看下一行或者前一行数据。对于其他关系数据库管理系统，可以使用标量子查询，尽管不是那么便利。对于本问题而言，当被迫要用标量子查询或自连接来解决问题时，解决方案就没有那么简单。
DB2、MySQL、PostgreSQL 和 SQL Server
用标量子查询取出紧随当前员工之后入职的员工的 HIREDATE，然后再用另一个标量子查询找出该员工的工资。

 1  select deptno,ename,hiredate,sal,
 2         coalesce(cast(sal-next_sal as char(10)),'N/A') as diff
 3    from (
 4  select e.deptno,
 5         e.ename,
 6         e.hiredate,
 7         e.sal,
 8         (select min(sal) from emp d
 9           where d.deptno=e.deptno
10             and d.hiredate =
11                  (select min(hiredate) from emp d
12                    where e.deptno=d.deptno
13                    and d.hiredate > e.hiredate)) as next_sal
14    from emp e
15         ) x

Oracle
使用窗口函数 LEAD OVER 读取与当前行相关的下一个员工的工资。

1 select deptno,ename,sal,hiredate,
2        lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
3   from (
4 select deptno,ename,sal,hiredate,
5        lead(sal)over(partition by deptno
6                          order by hiredate) next_sal
7   from emp
8        )

SQL 计算同一组或分区的行之间的差 扩展知识

DB2、MySQL、PostgreSQL 和SQL Server
首先使用标量子查询找出同一个部门里紧随当前员工之后入职的员工的 HIREDATE，本解决方案在标量子查询中使用了 MIN(HIREDATE) 来确保仅返回一个值，即使同一天入职的员工不止一个人，也只会返回一个值。

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

然后，使用另一个标量子查询来找出入职日期等于 NEXT_HIRE 的员工的工资。同样，本解决方案使用 MIN 函数来确保只返回一个值。

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

最后，计算出 SAL 和 NEXT_SAL 之间的差，并且使用 COALESCE 函数在适当的时候返回 N/A。因为减法运算的结果既有可能是数字，也有可能是 Null，所以必须将其转换为字符串，以便 COALESCE 函数可以正常运行。

select deptno,ename,hiredate,sal,
       coalesce(cast(sal-next_sal as char(10)),'N/A') as diff
  from (
select e.deptno,
       e.ename,
       e.hiredate,
       e.sal,
       (select min(sal) from emp d
         where d.deptno=e.deptno
           and d.hiredate =
                (select min(hiredate) from emp d
                  where e.deptno=d.deptno
                    and d.hiredate > e.hiredate)) as next_sal
  from emp e
       ) x
 order by 1
　
DEPTNO ENAME      HIREDATE           SAL DIFF
------ ---------- ----------- ---------- ---------
    10 CLARK      09-JUN-1981       2450 -2550
    10 KING       17-NOV-1981       5000 3700
    10 MILLER     23-JAN-1982       1300 N/A
    20 SMITH      17-DEC-1980        800 -2175
    20 ADAMS      12-JAN-1983       1100 N/A
    20 FORD       03-DEC-1981       3000 0
    20 SCOTT      09-DEC-1982       3000 1900
    20 JONES      02-APR-1981       2975 -25
    30 ALLEN      20-FEB-1981       1600 350
    30 BLAKE      01-MAY-1981       2850 1350
    30 MARTIN     28-SEP-1981       1250 300
    30 JAMES      03-DEC-1981        950 N/A
    30 TURNER     08-SEP-1981       1500 250
    30 WARD       22-FEB-1981       1250 -1600

![](https://img.geek-docs.com/sql/tip.png)　本解决方案使用了 `MIN(SAL)` 函数，这说明我们在某些情况下可能会不知不觉间把一些额外的业务逻辑引入查询，而我们却认为这只是一个纯粹的技术决策。如果一个给定的日期对应多个可能的工资值，我们应该选择最小值、最大值还是平均值呢？本例选择了最小值。在实际工作中，我可能更愿意将选择权交给提出报表请求的客户。

Oracle
首先使用窗口函数 LEAD OVER 为每个员工找出同部门中的“下一个”工资值。对于每个部门里最迟入职的员工，其 NEXT_SAL 列会是 Null。

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

然后，计算出同一个部门里每个员工与紧随其后入职的员工的工资差值。

select deptno,ename,sal,hiredate, sal-next_sal diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno order by hiredate) next_sal
  from emp
       )
　
DEPTNO ENAME             SAL HIREDATE          DIFF
------ ---------- ---------- ----------- ----------
    10 CLARK            2450 09-JUN-1981      -2550
    10 KING             5000 17-NOV-1981       3700
    10 MILLER           1300 23-JAN-1982
    20 SMITH             800 17-DEC-1980      -2175
    20 JONES            2975 02-APR-1981        -25
    20 FORD             3000 03-DEC-1981          0
    20 SCOTT            3000 09-DEC-1982       1900
    20 ADAMS            1100 12-JAN-1983
    30 ALLEN            1600 20-FEB-1981        350
    30 WARD             1250 22-FEB-1981      -1600
    30 BLAKE            2850 01-MAY-1981       1350
    30 TURNER           1500 08-SEP-1981        250
    30 MARTIN           1250 28-SEP-1981        300
    30 JAMES             950 03-DEC-1981

接下来，调用 NVL 函数，在 DIFF 等于 Null 时返回 N/A。为了做到这一点，必须先把 DIFF 值转换为字符串，否则 NVL 函数会执行失败。

select deptno,ename,sal,hiredate,
       nvl(to_char(sal-next_sal),'N/A') diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno order by hiredate) next_sal
  from emp
       )
　
DEPTNO ENAME             SAL HIREDATE    DIFF
------ ---------- ---------- ----------- ---------------
    10 CLARK            2450 09-JUN-1981 -2550
    10 KING             5000 17-NOV-1981 3700
    10 MILLER           1300 23-JAN-1982 N/A
    20 SMITH             800 17-DEC-1980 -2175
    20 JONES            2975 02-APR-1981 -25
    20 FORD             3000 03-DEC-1981 0
    20 SCOTT            3000 09-DEC-1982 1900
    20 ADAMS            1100 12-JAN-1983 N/A
    30 ALLEN            1600 20-FEB-1981 350
    30 WARD             1250 22-FEB-1981 -1600
    30 BLAKE            2850 01-MAY-1981 1350
    30 TURNER           1500 08-SEP-1981 250
    30 MARTIN           1250 28-SEP-1981 300
    30 JAMES             950 03-DEC-1981 N/A

最后，调用函数 LPAD 对 DIFF 值进行格式化。这是因为在默认情况下，数字是右对齐的，而字符串则是左对齐的。调用 LPAD 能让所有结果值都变成右对齐。

select deptno,ename,sal,hiredate,
       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno order by hiredate) next_sal
  from emp
       )
　
DEPTNO ENAME              SAL HIREDATE    DIFF
------ ---------- ---------- ----------- ----------
    10 CLARK            2450 09-JUN-1981      -2550
    10 KING             5000 17-NOV-1981       3700
    10 MILLER           1300 23-JAN-1982        N/A
    20 SMITH             800 17-DEC-1980      -2175
    20 JONES            2975 02-APR-1981        -25
    20 FORD             3000 03-DEC-1981          0
    20 SCOTT            3000 09-DEC-1982       1900
    20 ADAMS            1100 12-JAN-1983        N/A
    30 ALLEN            1600 20-FEB-1981        350
    30 WARD             1250 22-FEB-1981      -1600
    30 BLAKE            2850 01-MAY-1981       1350
    30 TURNER           1500 08-SEP-1981        250
    30 MARTIN           1250 28-SEP-1981        300
    30 JAMES             950 03-DEC-1981        N/A

本书的大部分实例都没有讨论“特例”（这是考虑到代码的可读性，也有利于我在写作过程中保持思路清晰），但是本例有必要讨论一下在使用 Oracle 的 LEAD OVER 函数时需要注意的重复项问题。对于 EMP 表里那些简单的示例数据而言，并不存在 HIREDATE 相同的行，这也是非常可能发生的状况。因而，正常情况下，我不会讨论如数据重复之类的特例（因为 EMP 表里并没有重复项）。但是，一旦涉及 LEAD 函数，有些读者很可能无法马上想到这一层（对于那些没有 Oracle 经验的读者尤其如此）。考虑如下所示的查询，该查询返回部门编号为 10 的员工之间的工资差距（按照入职先后排序计算出前后两人的工资差值）。

select deptno,ename,sal,hiredate,
       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno
                         order by hiredate) next_sal
  from emp
where deptno=10 and empno > 10
      )
　
DEPTNO ENAME    SAL HIREDATE    DIFF
------ ------ ----- ----------- ----------
    10 CLARK   2450 09-JUN-1981      -2550
    10 KING    5000 17-NOV-1981       3700
    10 MILLER  1300 23-JAN-1982        N/A

上述解决方案对于 EMP 表的现有数据而言毫无问题，但如果考虑重复行，就不对了。考虑如下所示的例子，有另外 4 人和员工 KING 同一天入职。

insert into emp (empno,ename,deptno,sal,hiredate)
values (1,'ant',10,1000,to_date('17-NOV-1981'))
　
insert into emp (empno,ename,deptno,sal,hiredate)
values (2,'joe',10,1500,to_date('17-NOV-1981'))
　
insert into emp (empno,ename,deptno,sal,hiredate)
values (3,'jim',10,1600,to_date('17-NOV-1981'))
　
insert into emp (empno,ename,deptno,sal,hiredate)
values (4,'jon',10,1700,to_date('17-NOV-1981'))
　
select deptno,ename,sal,hiredate,
       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno
                         order by hiredate) next_sal
  from emp
 where deptno=10
       )
　
DEPTNO ENAME    SAL HIREDATE    DIFF
------ ------ ----- ----------- ----------
    10 CLARK   2450 09-JUN-1981       1450
    10 ant     1000 17-NOV-1981       -500
    10 joe     1500 17-NOV-1981      -3500
    10 KING    5000 17-NOV-1981       3400
    10 jim     1600 17-NOV-1981       -100
    10 jon     1700 17-NOV-1981        400
    10 MILLER  1300 23-JAN-1982        N/A

我们看到，除了 JON 以外，其他在同一天（11 月 17 日）入职的员工竟然都在和另一个同时入职的人做工资比较！这是不正确的。所有在 11 月 17 日入职的员工都应该和 MILLER 做比较。以员工 ANT 为例，ANT 的 DIFF 值是 -500，这是因为其 SAL 值相较于 JOE 少 500。事实上，ANT 的 DIFF 值应该是 -300 才对，因为其 SAL 值比 MILLER 的少 300，依据 HIREDATE 的顺序，MILLER 才是紧随 ANT 之后入职的员工。上述查询结果的错误应该归因于 LEAD OVER 函数的默认行为方式。在默认情况下，LEAD OVER 函数只往前看一行。因此，对于员工 ANT 而言，基于 HIREDATE 的下一个 SAL 值是 JOE 的 SAL，因为 LEAD OVER 函数只会看下一行，并不会跳过重复项。幸运的是，Oracle 考虑到了这种情况，并允许我们通过传递一个额外的参数告诉 LEAD OVER 函数应该往前看几行。对于本例而言，只需要做一个计数：找出每一个在 11 月 17 日入职的员工和 1 月 23 日（MILLER 的 HIREDATE）之间的距离。下面的解决方案展示了如何实现这一点。

select deptno,ename,sal,hiredate,
       lpad(nvl(to_char(sal-next_sal),'N/A'),10) diff
  from (
select deptno,ename,sal,hiredate,
       lead(sal,cnt-rn+1)over(partition by deptno
                         order by hiredate) next_sal
  from (
select deptno,ename,sal,hiredate,
       count(*)over(partition by deptno,hiredate) cnt,
       row_number()over(partition by deptno,hiredate order by sal) rn
  from emp
 where deptno=10
       )
       )
　
DEPTNO ENAME    SAL HIREDATE    DIFF
------ ------ ----- ----------- ----------
    10 CLARK   2450 09-JUN-1981       1450
    10 ant     1000 17-NOV-1981       -300
    10 joe     1500 17-NOV-1981        200
    10 jim     1600 17-NOV-1981        300
    10 jon     1700 17-NOV-1981        400
    10 KING    5000 17-NOV-1981       3700
    10 MILLER  1300 23-JAN-1982        N/A

现在的解决方案是正确的了。我们看到，所有在 11 月 17 日入职的员工都改为和 MILLER 做比较了。从查询结果里可以看到，现在员工 ANT 的 DIFF 值是 -300，这正是我们希望的结果。你可能不理解传递给 LEAD OVER 的表达式；其实，CNT-RN+1 代表每一个在 11 月 17 日入职的员工到 MILLER 的距离。如下所示的内嵌视图展示了 CNT 和 RN 的值。

select deptno,ename,sal,hiredate,
       count(*)over(partition by deptno,hiredate) cnt,
       row_number()over(partition by deptno,hiredate order by sal) rn
  from emp
 where deptno=10
　
DEPTNO ENAME    SAL HIREDATE           CNT         RN
------ ------ ----- ----------- ---------- ----------
    10 CLARK   2450 09-JUN-1981          1          1
    10 ant     1000 17-NOV-1981          5          1
    10 joe     1500 17-NOV-1981          5          2
    10 jim     1600 17-NOV-1981          5          3
    10 jon     1700 17-NOV-1981          5          4
    10 KING    5000 17-NOV-1981          5          5
    10 MILLER  1300 23-JAN-1982          1          1

对于每一个员工而言，CNT 值表示有多少个相同的 HIREDATE，RN 值代表的是部门编号为 10 的员工的排名。排名基于 DEPTNO 和 HIREDATE 分组，因此只有在 HIREDATE 重复的时候，员工的排名才可能大于 1。排名基于 SAL 值排序（并不是必须这么做；只是基于 SAL 值排序比较方便，当然也可以选 EMPNO）。现在我们知道了有多少个重复项，以及每一个重复项对应的排名，它们到 MILLER 的距离就是重复项的数目减去当前排名，然后再加 1（CNT-RN+1）。距离计算的结果以及它对 LEAD OVER 的效果显示如下。

select deptno,ename,sal,hiredate,
       lead(sal)over(partition by deptno
                         order by hiredate) incorrect,
       cnt-rn+1 distance,
       lead(sal,cnt-rn+1)over(partition by deptno
                         order by hiredate) correct
  from (
select deptno,ename,sal,hiredate,
       count(*)over(partition by deptno,hiredate) cnt,
       row_number()over(partition by deptno,hiredate
                            order by sal) rn
  from emp
 where deptno=10
       )
　
DEPTNO ENAME    SAL HIREDATE     INCORRECT   DISTANCE    CORRECT
------ ------ ----- ----------- ---------- ---------- ----------
    10 CLARK   2450 09-JUN-1981       1000          1       1000
    10 ant     1000 17-NOV-1981       1500          5       1300
    10 joe     1500 17-NOV-1981       1600          4       1300
    10 jim     1600 17-NOV-1981       1700          3       1300
    10 jon     1700 17-NOV-1981       5000          2       1300
    10 KING    5000 17-NOV-1981       1300          1       1300
    10 MILLER  1300 23-JAN-1982                     1

现在我们清楚地看到了当传递正确的距离值给 LEAD OVER 函数时会得到什么样的结果。INCORRECT 列代表了传递默认距离 1 给 LEAD OVER 函数时得到的返回值。CORRECT 列代表了为每个重复的 HIREDATE 对应的员工传递到 MILLER 的实际值给 LEAD OVER 函数时得到的返回值。至此，剩下要做的就是为每一行找出 CORRECT 和 SAL 之间的差值，这在前面已经介绍过了。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

SQL 实例