赛捷软件论坛's Archiver

vicky.yu 发表于 2019-2-15 11:09

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

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 je in (select top 2 je from hard b where a.qu=b.qu order by je)

页: [1]

Powered by Discuz! Archiver 7.2  © 2001-2009 Comsenz Inc.