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

[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及以上版本： 
 
--要求1：
;with aaa as
(
select ROW_NUMBER() over(partition by name order by data desc) as rowindex,* from t1
)
--select * from aaa
select name from aaa where rowindex<=2 and sta='Pass' group by name having COUNT(name)=2

----------------------
name
a
c
d
e

--要求2：
;with bbb as
(
select ROW_NUMBER() over(partition by name order by data desc) as rowindex,* from t1
)
select a.name from bbb as a inner join bbb as b  
on a.name=b.name and a.rowindex=1 and a.sta='Pass' and b.rowindex=2 and b.sta='Fail'

-----------------------
name
b
w
y