找出最近的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为此员工倒数第二次调薪后的薪资。

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

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

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种方法的执行计划，哪个效率高也比较明显。