sql 排序语句,查询数据的最大排序问题(只能用一条语句写)

查询数据的最大排序问题(只能用一条语句写) - 应用软件 - 电脑教程网

查询数据的最大排序问题(只能用一条语句写)

日期:2007-05-07   荐:

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)
标签: