这两天在《SQL puzzles and answers》一书中看到这个‘找出最近的2个薪资问题’，此问题是一个非常典型的场景。在这里自己试着给出2个我一下能想到的解决方案，与大家分享（代码在SQL Server 2008下，测试通过）。值得一提的是原书中给出了9种解决方案，有兴趣的可以去看看。

问题描述

我们有一张Salaries表，其中记录了员工薪资变化的情况。创建Salaries表脚本如下（此脚本来自与原书）：

01
create table Salaries 

02
( 

03
    emp_name char(10) not null, 

04
    sal_date date not null, 

05
    sal_amt decimal(8,2) not null, 

06
    primary key(emp_name, sal_date) 

07
); 

08
  
09
insert into

10
    Salaries 

11
values

12
    ('Tom', '1996-06-20', 500.00), 

13
    ('Tom', '1996-08-20', 700.00), 

14
    ('Tom', '1996-10-20', 800.00), 

15
    ('Tom', '1996-12-20', 900.00), 

16
    ('Dick', '1996-06-20', 500.00), 

17
    ('Harry', '1996-07-20', 500.00), 

18
    ('Harry', '1996-09-20', 700.00);

其中emp_name为员工名，sal_date为调薪日期，sal_amt为调薪后的薪资。

现在的问题是要得到一个结果集，每一个员工一条记录。结果集包含5列，第1列为emp_name,第2列date1为此员工最近一次调薪的日期，第3列sal1为此员工最近一次调薪后的薪资，第4列date2为此员工倒数第二次调薪的日期，第5列sal2为此员工倒数第二次调薪后的薪资。

对于上例期望得到的结果为：

emp_name	date1	sal1	date2	sal2
Tom	1996-12-20	900.00	1996-10-20	800.00
Harry	1996-09-20	700.00	1996-07-20	500.00
Dick	1996-06-20	500.00	NULL	NULL

解决方案1

01
select

02
    S1.emp_name, 

03
    S1.sal_date date1, 

04
    S1.sal_amt sal1, 

05
    S2.sal_date date2, 

06
    S2.sal_amt sal2 

07
from

08
    Salaries S1 

09
    left join

10
    Salaries S2 

11
    on

12
        S1.emp_name = S2.emp_name 

13
        and

14
        S1.sal_date > S2.sal_date 

15
where

16
    S1.sal_date = (select MAX(S3.sal_date) from Salaries S3 where S3.emp_name = S1.emp_name) 

17
    and

18
    ( 

19
        S2.sal_date = (select MAX(S4.sal_date) from Salaries S4 where S4.emp_name = S1.emp_name and S4.sal_date < S1.sal_date) 

20
        or

21
        S2.sal_date is null

22
    );

此方案完全使用子查询完成。其中需要注意的是where条件中的or S2.sal_date is null子句，如果没有此条件就会丢失一条记录。

解决方案2

01
With SalWithRN as

02
( 

03
    select

04
        emp_name, 

05
        sal_date, 

06
        sal_amt, 

07
        ROW_NUMBER() over(partition by emp_name order by sal_date desc) rn 

08
    from

09
        Salaries 

10
) 

11
select

12
    emp_name, 

13
    MAX(case when rn = 1 then sal_date else null end) date1, 

14
    MAX(case when rn = 1 then sal_amt else null end) sal1, 

15
    MAX(case when rn = 2 then sal_date else null end) date2, 

16
    MAX(case when rn = 2 then sal_amt else null end) sal2 

17
from

18
    SalWithRN 

19
group by

20
    emp_name;

第二种方案使用到了ROW_NUMBER()分析函数，其中的聚合计算也是一个小技巧。

大家还可以比较这2种方法的执行计划，哪个效率高也比较明显。

ITPub博客

找出最近的2个薪资问题

最新文章