CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
insert into hard values ('A','1',3) insert into hard values ('A','2',4) insert into hard values ('A','4',2) insert into hard values ('A','6',9) insert into hard values ('B','1',4) insert into hard values ('B','2',5) insert into hard values ('B','3',6) insert into hard values ('C','3',4) insert into hard values ('C','6',7) insert into hard values ('C','2',3)
要求查询出来的结果如下：
qu co je ----------- ----------- ----- A 6 9 A 2 4 B 3 6 B 2 5 C 6 7 C 3 4
就是要按qu分组，每组中取je最大的前2位！！ 而且只能用一句sql语句！！！
select * from hard a where (select count(*) from hard b where a.qu=b.qu and b.je>=a.je)<=2 ORDER BY qu,je DESC选出一条记录, 然后做循环. 这么写会好懂一些? select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
可以这样写： select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je desc)