请教一个分组排序的 SQL语句

[chufeng_007]

现在在一个成绩表(学生姓名，课程名称，成绩) tb_score(studentName,subjectName,score)
要求查询出此表中各课成绩前三名的记录

[acmly]

SELECT studentName, subjectName, score FROM tb_score a WHERE (SELECT count(*) FROM tb_score b WHERE b.score > a.score and b.subjectName = a.subjectName) <=3;

[超叔csdn]

select studentName, subjectName, score
from (SELECT rank() over(partition by a.subjectName order by a.score desc) as num,studentName, subjectName, score
FROM tb_score a
) b
where b.num <= 3

[超叔csdn]

acmly(Paolo) 的方法也很好.只是好象是前4名的

[danisluo]

select top 3 from tb_score
group by score desc

[acmly]

不错不错，谢谢sbaz(万神渡劫)，应该写成：
SELECT studentName, subjectName, score FROM tb_score a WHERE (SELECT count(*) FROM tb_score b WHERE b.score > a.score and b.subjectName = a.subjectName) <3;

[超叔csdn]

acmly(Paolo)
我只想到了用分析函数来做,我当时看到你的SQL才感觉到高手就是不一样啊.
虽然有点小失误,但方法还是正确的.

[SInoyew]

两个写的不错。

[TWWH]

acmly(Paolo)的语句正确的。
改进一下，就看得更清楚了：

SELECT subjectName,studentName, score FROM
tb_score a
WHERE (SELECT count(*) FROM tb_score b WHERE b.score > a.score and
b.subjectName = a.subjectName) <3
ORDER BY SUBJECTNAME,SCORE DESC,STUDENTNAME

这是按每门课的前3名排序后的结果。

[shyming]

不錯，不錯!