求助 一个简单的查询
name time grade
张三 2009 10
张三 2010 20
张三 2011 30
李四 2010 50
李四 2011 60
李四 2011 70
王五 2011 80

用sql语句变成下面这张表：
name time grade
张三 2011 30
李四 2011 60

取出2011之前有过记录的 在2011的第一个写入数据库的grade

主要是2011第一个grade怎么刷选出

谢谢各位了！！
[解决办法]
with tb(name,time,grade)
as(
select '张三',2009,10 union all
select '张三',2010,20 union all
select '张三',2011,30 union all
select '李四',2010,50 union all
select '李四',2011,60 union all
select '李四',2011,70 union all
select '王五',2011,80)
select name,time,grade from(
select row=row_number()over(partition by name order by getdate()),name,grade,time from tb tb1 where time=2011 and (select count(1) from tb tb2 where time<2011 and tb1.name=tb2.name)>0
)t where row=1 order by grade
[解决办法]

;with cte1 as(
select *,rn=row_number() over(order by getdate()) from tb
),
cte2 as
(select *,gid=rn-(select count(1) from cte1 where time=t.time and rn<t.rn)
from cte1 t
)
select name,time,grade
from cte2 t
where not exists(select 1 from cte2 where time=2011 and gid=t.gid and rn<t.rn)
and time=2011

select * from tb as a where name in(select name from tb where year(time)<2011)
and exists
(select 1 from tb where a.name=name and year(time)>=2011 
group by name having min(time)=a.time)

create table #temp
 (
    name varchar(20) not null,
    [time] datetime not null,
    grade int 
 )
 insert into #temp
 select '张三','2009',10 union all
 select '张三','2010',20 union all
 select '张三','2011',30 union all
 select '李四','2010',50 union all
 select '李四','2011',60 union all
 select '李四','2011',70 union all
 select '王五','2011',80

select 
    name,
    [time],
    grade  
 
from(
  select 
row=row_number() over(partition by name order by getdate()),
name,
grade,
[time]
  from #temp tb1 
  where [time]='2011' )t
where row=1 
order by grade
name                 time                    grade
-------------------- ----------------------- -----------
张三                   2011-01-01 00:00:00.000 30
李四                   2011-01-01 00:00:00.000 60
王五                   2011-01-01 00:00:00.000 80

(3 行受影响)