[SQL]请诸位大侠指点帮助，卡住了：（_Sql Server

[SQL]请各位大侠指点帮助，卡住了：（
Name Status Data
a Pass 2012-05-12
a Pass 2012-05-11
a Fail 2012-05-09
a Pass 2012-05-10
b Pass 2012-05-12
b Fail 2012-05-11
b Pass 2012-05-10
b Pass 2012-05-09
b Pass 2012-05-08
c Pass 2012-05-09
c Pass 2012-05-12
d Pass 2012-05-09
d Pass 2012-05-08
e Pass 2012-05-12
e Pass 2012-05-11
e Pass 2012-05-09
w Pass 2012-05-12
w Fail 2012-05-12
y Pass 2012-05-12
y Fail 2012-05-11
y Fail 2012-05-09
y Fail 2012-05-06
y Fail 2012-05-05
z Fail 2012-05-06
z Fail 2012-05-05
1.根据该表查询出“最近”两次状态都是Pass的名字？
（预期结果）查询结果如下：
a
c
d
e
2.根据该表查询出“最近”的两次状态中，最近的一次是Pass ，另一个为Fail
（预期结果）查询结果如下：
b
w
y

------解决方案--------------------

SQL code

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([Name] varchar(1),[Status] varchar(4),[Data] datetime)insert [tb]select 'a','Pass','2012-05-12' union allselect 'a','Pass','2012-05-11' union allselect 'a','Fail','2012-05-09' union allselect 'a','Pass','2012-05-10' union allselect 'b','Pass','2012-05-12' union allselect 'b','Fail','2012-05-11' union allselect 'b','Pass','2012-05-10' union allselect 'b','Pass','2012-05-09' union allselect 'b','Pass','2012-05-08' union allselect 'c','Pass','2012-05-09' union allselect 'c','Pass','2012-05-12' union allselect 'd','Pass','2012-05-09' union allselect 'd','Pass','2012-05-08' union allselect 'e','Pass','2012-05-12' union allselect 'e','Pass','2012-05-11' union allselect 'e','Pass','2012-05-09' union allselect 'w','Pass','2012-05-12' union allselect 'w','Fail','2012-05-12' union allselect 'y','Pass','2012-05-12' union allselect 'y','Fail','2012-05-11' union allselect 'y','Fail','2012-05-09' union allselect 'y','Fail','2012-05-06' union allselect 'y','Fail','2012-05-05' union allselect 'z','Fail','2012-05-06' union allselect 'z','Fail','2012-05-05'goselect namefrom(  select * from tb t   where data in(select top 2 data from tb where name=t.name order by data desc)) twhere status='pass'group by namehaving count(1)>1/**name----acde(4 行受影响)**/select namefrom(  select * from tb t   where data in(select top 2 data from tb where name=t.name order by data desc)) tgroup by namehaving count(distinct status)>1/**name----bwy(3 行受影响)**/
------解决方案--------------------
create table t1
(
	name varchar(2),
	sta varchar(5),
	data date
)
insert into t1
select 'a','Pass','2012-05-12' union all
select 'a','Pass','2012-05-11' union all
select 'a','Fail','2012-05-09' union all
select 'a','Pass','2012-05-10' union all
select 'b','Pass','2012-05-12' union all
select 'b','Fail','2012-05-11' union all
select 'b','Pass','2012-05-10' union all
select 'b','Pass','2012-05-09' union all
select 'b','Pass','2012-05-08' union all
select 'c','Pass','2012-05-09' union all
select 'c','Pass','2012-05-12' union all
select 'd','Pass','2012-05-09' union all
select 'd','Pass','2012-05-08' union all
select 'e','Pass','2012-05-12' union all
select 'e','Pass','2012-05-11' union all
select 'e','Pass','2012-05-09' union all
select 'w','Pass','2012-05-12' union all
select 'w','Fail','2012-05-12' union all
select 'y','Pass','2012-05-12' union all
select 'y','Fail','2012-05-11' union all
select 'y','Fail','2012-05-09' union all
select 'y','Fail','2012-05-06' union all
select 'y','Fail','2012-05-05' union all
select 'z','Fail','2012-05-06' union all
select 'z','Fail','2012-05-05'
select * from t1

MSSQL2005及以上版本：