--程序员们在编写一个雇员报表，他们需要得到每个雇员当前及历史工资状态的信息，
--以便生成报表。报表需要显示每个人的晋升日期和工资数目。
--如果将每条工资信息都放在结果集的一行中，并让宿主程序去格式化它。
--应用程序的程序员都是一帮懒人，他们需要在每个雇员的一行上得到当前
--和历史工资信息。这样就可以写一个非常简单的循环语句。
---示例：
create table salaries
( name nvarchar(50) not null,
sal_date date not null,
salary money not null,
)
go
ALTER TABLE [dbo].salaries ADD CONSTRAINT [PK_salaries] PRIMARY KEY CLUSTERED
(
name ,sal_date asc
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

----插入数据
insert into salaries
select 'TOM','2010-1-20',2000
union
select 'TOM','2010-6-20',2300
union
select 'TOM','2010-12-20',3000
union
select 'TOM','2011-6-20',4000
union
select 'Dick','2011-6-20',2000
union
select 'Harry','2010-6-20',2000
union
select 'Harry','2011-6-20',2000

go

----方法一、使用left join 连接进行查询（sql 2000及以上版本）
select b.name,b.maxdate,y.salary,b.maxdate2,z.salary
from(select a.name,a.maxdate,MAX(x.sal_date) as maxdate2
from(select w.name,MAX(w.sal_date) as maxdate
from salaries as w
group by w.name) as a
left outer join salaries as x on a.name=x.name and a.maxdate>x.sal_date
group by a.name,a.maxdate) as b
left outer join salaries as y
on b.name=y.name and b.maxdate=y.sal_date
left outer join salaries as z
on b.name=z.name and b.maxdate2=z.sal_date

go
----方法二、这个方法是对每个雇员中的行进行编号，然后取出两个雇用日期最近的日期，

---（sql 2005以上版本）
select s1.name,
MAX(case when rn=1 then sal_date else null end) as curr_date,
MAX(case when rn=1 then salary else null end) as curr_salary,
MAX(case when rn=2 then sal_date else null end) as prev_date,
MAX(case when rn=2 then salary else null end) as curr_salary
from (select name,sal_date,salary, RANK() over(partition by name order by sal_date desc) rn
from salaries
) s1 where rn