求sql

[caoyang0299]

wl， rq， ，sl ，qc
a 711 6 3
a 522 4 0
a 401 22 3
b 703 44 3
b 201 11 0
b 302 35 4
a 211 3 0
b 315 33 0
数据如上，首先取qc=0的最大rq，然后把大于rq的记录取出来
也就是下面的结果
a， 711，6，3
b， 703，44，3
求sql，谢谢。sql2000

[道玄希言]

select * from tbl where rq > (
select max(rq) from tbl where qc = 0)

[yooq_csdn]

引用 1 楼 yangb0803 的回复:

select * from tbl where rq > (
select max(rq) from tbl where qc = 0)

+1

[满天飞雪No1]

create table #a(wl varchar(2),rq int,sl int,qc int) insert into #a(wl,rq ,sl,qc) values('a',711,6,3),('a',522,4,0),('a',401,22,3),('b',703,44,3),('b',201,11,0),('b',302,35,4) ,('a',211,3,0),('b',315,33,0) select * from #a where rq >(select MAX(rq) from #a where qc=0 )

[caoyang0299]

要按wl分组的，要的到的结果 a， 711，6，3 b， 703，44，3

[kingzhh]

/* SQL2008 */ declare @ta table(wl nvarchar(1), rq int, sl int, qc int); insert into @ta(wl,rq,sl,qc) values ('a',711,6,3), ('a',522,4,0), ('a',401,22,3), ('b',703,44,3), ('b',201,11,0), ('b',302,35,4), ('a',211,3,0), ('b',315,33,0), with tb as (select wl,rq from (select *,ROW_NUMBER() over(partition by wl order by rq desc) as rn from @ta where qc=0) as t where rn=1) select ta.wl,ta.rq,ta.sl,ta.qc from @ta as ta,tb where ta.wl=tb.wl and ta.rq>tb.rq

[caoyang0299]

2000,我是用exists，取每个wl的rq最大值，然后和原始表join，取的大于rq的记录。 不过太慢啦，有没有好的办法。代码是下面的 select * from kch05 hh inner join ( select * from ( select * from kch05 where kcwpdm in (select kcwpdm from kct05 k5 where k5.KCCKDM IN ('BJ01', 'BJ02', 'BJ03', 'BJ04') and k5.KCZKSL <> 0 and k5.kcqcsl<>0) and kcqcsl=0 ) h05 where not exists ( select 1 from (select * from kch05 where kcwpdm in (select kcwpdm from kct05 k5 where k5.KCCKDM IN ('BJ01', 'BJ02', 'BJ03', 'BJ04') and k5.KCZKSL <> 0 and k5.kcqcsl<>0) and kcqcsl=0) hh05 where h05.xtcznd*100+h05.xthsqm<hh05.xtcznd*100+hh05.xthsqm and h05.kcwpdm=hh05.kcwpdm) )hh1 on hh.kcwpdm=hh1.kcwpdm where hh.xtcznd*100+hh.xthsqm>hh1.xtcznd*100+hh1.xthsqm