【MySQL】学生成绩

1. 统计每个人的总成绩排名

select stu.`name`,sum(stu.score) as totalscore
from stu
GROUP BY `name`
order by totalscore

2. 统计每门课程的总分排序
   

SELECT
	stu.class,
	sum(stu.score) as totalscore
FROM
	stu 
GROUP BY
	stu.class 
ORDER BY
	totalscore desc

结果

3.求每门课的前三名

SELECT
	* 
FROM
	stu a 
WHERE
	( SELECT count(*) FROM stu b WHERE b.class = a.class AND a.score < b.score ) < 3 
ORDER BY
	a.class,
	a.score DESC

或

SELECT
	* 
FROM
	stu a 
WHERE
	( SELECT count(1) FROM stu b WHERE b.class = a.class AND a.score < b.score ) < 3 
ORDER BY
	a.class,
	a.score DESC

这个方法会保留重复成绩