互异地全天在线

11

d

有群吗

有没有用

SELECT xs.`学号`,xs.`姓名`,SUM(xs_kc.`成绩`) AS 总分 FROM xs_kc JOIN xs ON xs_kc.`学号`=xs.`学号`GROUP BY xs.`学号`,xs.`姓名`ORDER BY 总分 DESC;
-- 8.查询学生成绩总分高于200分的学生信息；
SELECT xs.* FROM xs JOIN (SELECT 学号, SUM(成绩) AS 总分 FROM xs_kc GROUP BY 学号 HAVING SUM(成绩) > 200) AS high_scorers ON xs.学号 = high_scorers.学号;
-- 9.统计没有参加考试的学生的名单；
SELECT xs.学号, xs.姓名 FROM xs LEFT JOIN xs_kc ON xs.学号 = xs_kc.学号 WHERE xs_kc.成绩 IS NULL;
-- 10.显示总分前三名的学生的学号，姓名和总成绩；
SELECT xs.学号, xs.姓名, SUM(xs_kc.成绩) AS 总成绩 FROM xs_kc JOIN xs ON xs_kc.学号 = xs.学号 GROUP BY xs.学号, xs.姓名 ORDER BY 总成绩 DESC LIMIT 3;