转载自：http://www.cnblogs.com/DBFocus/archive/2010/09/23/1833375.html

这两天在《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()分析函数，其中的聚合计算也是一个小技巧。

解决方案3

SELECT T1.emp_Name, T2.date1, T1.Sal_Amt AS Sal1, T2.date2, T3.Sal_Amt AS Sal2 FROM Salaries AS T1 INNER JOIN (SELECT S1.emp_Name, MAX(S1.sal_Date) AS date1, MAX(S2.sal_Date) AS date2 FROM Salaries AS S1 LEFT OUTER JOIN Salaries AS S2 ON S1.emp_Name = S2.emp_Name AND S1.sal_Date > S2.sal_Date GROUP BY S1.emp_Name) AS T2 ON T1.emp_Name = T2.emp_Name AND T1.sal_Date = T2.date1 LEFT OUTER JOIN Salaries AS T3 ON T2.emp_Name = T3.emp_Name AND T2.date2 = T3.sal_Date