create table score(
id int,
class varchar(20),
name varchar(200),
score int
)

insert into score values(1, '一班', 'A1', 60)
insert into score values(2, '一班', 'A2', 70)
insert into score values(3, '一班', 'A3', 80)
insert into score values(4, '一班', 'A4', 90)
insert into score values(5, '一班', 'A5', 85)
insert into score values(6, '二班', 'B1', 60)
insert into score values(7, '二班', 'B2', 61)
insert into score values(8, '二班', 'B3', 62)
insert into score values(9, '二班', 'B4', 63)
insert into score values(10, '二班', 'B5', 65)
insert into score values(11, '二班', 'B6', 66)
insert into score values(12, '二班', 'B7', 68)
insert into score values(13, '二班', 'B8', 70)
insert into score values(14, '二班', 'B9', 80)
insert into score values(15, '二班', 'C1', 78)

select * from (
select sc.class, sc.name, sc.score, (select COUNT(*) from score a where sc.class= a.class
and a.score > sc.score) cnt from score sc) as t where t.cnt <= 2
order by class, score desc

比起分析函数，这个效率肯定较差

ITPub博客

不用分析函数求出每组前几名

最新文章