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

效果：