学生表
成绩表
实现代码:
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