SQL：求总分最高的学生

CREATE TABLE course ( id INTEGER PRIMARY KEY, NAME TEXT NOT NULL );
CREATE TABLE student ( id INTEGER PRIMARY KEY, NAME TEXT NOT NULL );
CREATE TABLE score ( id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL, student_id INTEGER NOT NULL, score INTEGER NOT NULL );

INSERT INTO course VALUES(1,'语文'),(2,'数学'),(3,'外语');
INSERT INTO student VALUES(1,'小张'),(2,'小王'),(3,'小马');

INSERT INTO score VALUES(1 ,1 ,1 ,80),(2 ,2 ,1 ,90),(3, 3, 1, 70);
INSERT INTO score VALUES(4 ,1 ,2 ,70),(5 ,2 ,2 ,90),(6, 3, 2, 80);
INSERT INTO score VALUES(7 ,1 ,3 ,80),(8 ,2 ,3 ,60),(9, 3, 3, 70);

-- 求总分最高的学生  （搞清楚是要全部学科的总分，还是单科总分最高？）
-- 思路：使用SUM函数 + 学生ID分组获取最高分
SELECT SUM(score) from score GROUP BY student_id ORDER BY SUM(score) desc  LIMIT 1

-- 这样只能查出一个人的最高分的，但是万一最高分有两个人或更多呢？     查出最高分所有的student
SELECT student_id from score GROUP BY student_id HAVING SUM(score) = (SELECT SUM(score) from score GROUP BY student_id ORDER BY SUM(score) desc  LIMIT 1)

-- 然后join 查出最高分的学生
SELECT s.name, t.score from student s RIGHT JOIN (SELECT student_id, SUM(score) score from score GROUP BY student_id HAVING SUM(score) = (SELECT SUM(score) from score GROUP BY student_id ORDER BY SUM(score) desc  LIMIT 1)) t ON s.id = t.student_id;

-- 如果需求是求单科最高分，该怎么写？
-- 先根据course_id分组，求出单科最高分
SELECT course_id, MAX(score) max_score from score GROUP BY course_id

-- 然后再查出 取得这个最高分的所有学生
SELECT student_id , s1.course_id, s2.max_score from score s1 RIGHT JOIN (SELECT course_id, MAX(score) max_score from score GROUP BY course_id) s2 
ON s1.score = s2.max_score and s1.course_id = s2.course_id;

-- 最后关联学生表和课程表 查找出对应的名字
SELECT s.name, c.name, s2.max_score from score s1 RIGHT JOIN (SELECT course_id, MAX(score) max_score from score GROUP BY course_id) s2 
ON s1.score = s2.max_score and s1.course_id = s2.course_id
LEFT JOIN student s ON s.id = s1.student_id
LEFT JOIN course c ON c.id = s1.course_id