请教高难度的SQL查询。

[jixiang8]

请选出每个班级的前三名的学号和总分数，
记录如下:
学号 班级 英语 语文 总分
1 1-1 98 88 521
2 1-1 95 86 500
3 1-2 94 77 512
4 1-4 75 71 491
5 1-2 81 81 490
6 1-1 91 91 580
123 1-2 86 77 498
234 1-1 76 78 412
145 1-3 69 71 408
162 1-3 69 81 428
141 1-5 80 80 488

结果如：
班级　　　学号　　　总分　　名次
1-1 6 580 1
1-1 1 521 2
1-1 2 500 3
1-2 3 512 1
1-2 5 490 2
1-2 234 485 3
1-3 123 498 1
1-3 162 428 1


表结构如下：
create table TBL_SCORE
(
FID NUMBER not null,
FCLASSID VARCHAR2(16),
CHINESE_SCORE NUMBER,
ENGLISH_SCORE NUMBER,
SUM_SCORE NUMBER
);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (1, '1-1', 98, 88, 521);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (2, '1-1', 95, 86, 500);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (3, '1-2', 94, 77, 512);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (4, '1-4', 75, 71, 491);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (5, '1-2', 81, 81, 490);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (6, '1-1', 91, 91, 580);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (123, '1-2', 86, 77, 498);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (234, '1-1', 76, 78, 412);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (145, '1-3', 69, 71, 408);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (162, '1-3', 69, 81, 428);
insert into TBL_SCORE (FID, FCLASSID, CHINESE_SCORE, ENGLISH_SCORE, SUM_SCORE)
values (141, '1-5', 80, 80, 488);

[HelloWorld_001]

select FCLASSID,FID,ENGLISH_SCORE,rn from (select FID,FCLASSID,ENGLISH_SCORE,row_number() over (partition by FCLASSID order by SUM_SCORE desc ) as rn from TBL_SCORE ) t
where rn<=3

已测试

楼主好人啊,写测试数据!-_-

[HelloWorld_001]

不好意思,太匆忙了
select 中的字段缺少了 SUM_SCORE,楼主自己加上就可以了

[jixiang8]

SELECT *
FROM (SELECT t.Fclassid, t.Sum_Score, Row_Number() Over(PARTITION BY t.Fclassid ORDER BY t.Sum_Score DESC) Rn
FROM Tbl_Score t)
WHERE Rn <4

[facome]

[Quote=引用 3 楼 的回复:]

SELECT *
FROM (SELECT t.Fclassid, t.Sum_Score, Row_Number() Over(PARTITION BY t.Fclassid ORDER BY t.Sum_Score DESC) Rn
FROM Tbl_Score t)
WHERE Rn <4
[/Quote]
顶