【MySQL】学生成绩

统计每个人的总成绩排名

select stu.`name`,sum(stu.score) as totalscore

from stu

GROUP BY `name`

order by totalscore

统计每门课程的总分排序

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

这个方法会保留重复成绩

巴特西

【MySQL】学生成绩

最新文章

热门文章