Mysql 降序查询学生成绩列表（含平均分 总分）经验总结

学生表

成绩表

实现代码：

SELECT
	a1.student_id '学号',
	d.student_name '姓名',
	a1.score 'Java基础',
	a2.score 'Java高级',
	a3.score '前端',
	b.sumscore '总成绩',
	c.avgscore '平均分' 
FROM
	( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 1 ) a1
	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 2 ) a2 ON a1.student_id = a2.student_id
	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 3 ) a3 ON a3.student_id = a2.student_id
	LEFT JOIN ( SELECT ss.student_id, sum( ss.score ) sumscore FROM student_socre ss GROUP BY ss.student_id ) b ON a3.student_id = b.student_id
	LEFT JOIN ( SELECT ss.student_id,CAST(AVG( score ) AS DECIMAL ( 10, 2 )) avgscore FROM student_socre ss 
	GROUP BY ss.student_id) c ON c.student_id = b.student_id
	LEFT JOIN ( SELECT s.student_id, s.student_name FROM student s GROUP BY s.student_id ) d ON c.student_id = d.student_id 
ORDER BY b.sumscore DESC

效果：

只显示个别班级的学生（例子显示一班的学生）

实现代码

SELECT
	a1.student_id '学号',
	d.student_name '姓名',
	a1.score 'Java基础',
	a2.score 'Java高级',
	a3.score '前端',
	b.sumscore '总成绩',
	c.avgscore '平均分'	
FROM
( SELECT s.student_id, s.student_name FROM student s WHERE class_id=1 GROUP BY s.student_id ) d 
	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 1 ) a1 ON a1.student_id = d.student_id
	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 2 ) a2 ON a1.student_id = a2.student_id
	LEFT JOIN ( SELECT ss.student_id, ss.score FROM student_socre ss WHERE ss.course_id = 3 ) a3 ON a3.student_id = a2.student_id
	LEFT JOIN ( SELECT ss.student_id, sum( ss.score ) sumscore FROM student_socre ss GROUP BY ss.student_id ) b ON a3.student_id = b.student_id
	LEFT JOIN ( SELECT ss.student_id,CAST(AVG( score ) AS DECIMAL ( 10, 2 )) avgscore FROM student_socre ss 
	GROUP BY ss.student_id) c ON c.student_id = b.student_id
ORDER BY b.sumscore DESC

效果：

若不排序 简化版

实现代码

SELECT
	s.student_id AS 学号,
	s.student_name AS 姓名,
	MAX( CASE WHEN ss.course_id = 1 THEN score ELSE 0 END ) AS Java基础,
	MAX( CASE WHEN ss.course_id = 2 THEN score ELSE 0 END ) AS Java高级,
	MAX( CASE WHEN ss.course_id = 3 THEN score ELSE 0 END ) AS 前端,
	CAST(
	AVG( score ) AS DECIMAL ( 10, 2 )) AS 平均分,
	SUM( score ) AS 总分 
FROM student s
	LEFT JOIN student_socre ss ON s.student_id = ss.student_id 
GROUP BY s.student_id

效果：