求助 一个简单的查询
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 行受影响)
