不用分析函式求出每組前幾名

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部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-1062909/，如需轉載，請註明出處，否則將追究法律責任。