找出最近的2个薪资问题

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

问题描述

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

create table Salaries
(
        emp_name char(10) not null,
        sal_date date not null,
        sal_amt decimal(8,2) not null,
        primary key(emp_name, sal_date)
);

insert into
        Salaries
values
        ('Tom', '1996-06-20', 500.00),
        ('Tom', '1996-08-20', 700.00),
        ('Tom', '1996-10-20', 800.00),
        ('Tom', '1996-12-20', 900.00),
        ('Dick', '1996-06-20', 500.00),
        ('Harry', '1996-07-20', 500.00),
        ('Harry', '1996-09-20', 700.00);

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

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

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

解决方案1

select
        S1.emp_name,
        S1.sal_date date1,
        S1.sal_amt sal1,
        S2.sal_date date2,
        S2.sal_amt sal2
from
        Salaries S1
        left join
        Salaries S2
        on
                S1.emp_name = S2.emp_name
                and
                S1.sal_date > S2.sal_date
where
        S1.sal_date = (select MAX(S3.sal_date) from Salaries S3 where S3.emp_name = S1.emp_name)
        and
        (
                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)
                or
                S2.sal_date is null
        );

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

解决方案2

With SalWithRN as
(
        select
                emp_name,
                sal_date,
                sal_amt,
                ROW_NUMBER() over(partition by emp_name order by sal_date desc) rn
        from
                Salaries
)
select
        emp_name,
        MAX(case when rn = 1 then sal_date else null end) date1,
        MAX(case when rn = 1 then sal_amt else null end) sal1,
        MAX(case when rn = 2 then sal_date else null end) date2,
        MAX(case when rn = 2 then sal_amt else null end) sal2
from
        SalWithRN
group by
        emp_name;

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

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