面试题：SQLite 多表联合过滤、限定与排序的优化

Question

假设有两个表，'students' 表包含字段'student_id'（整数类型，主键），'student_name'（文本类型），'class_id'（整数类型）；'scores' 表包含字段'score_id'（整数类型，主键），'student_id'（整数类型，外键关联'students' 表的'student_id'），'course'（文本类型），'score'（浮点数类型）。现在要查询每个班级数学成绩排名前三的学生信息（包括学生姓名、班级ID和数学成绩），请编写高效的SQLite语句实现此需求，并解释你的优化思路。

Answer 1

SQL 语句

WITH RankedScores AS (
    SELECT 
        s.student_name,
        s.class_id,
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY s.class_id ORDER BY sc.score DESC) AS rank
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        sc.course = '数学'
)
SELECT 
    student_name,
    class_id,
    score
FROM 
    RankedScores
WHERE 
    rank <= 3;

优化思路

1. 窗口函数使用：通过 ROW_NUMBER() 窗口函数，在每个班级内按数学成绩降序排名。这样可以在一个查询中计算出每个学生在班级内的排名，避免多次扫描表。
2. 公用表表达式（CTE）：使用 CTE RankedScores 来存储临时结果，使得主查询更加简洁易读。同时，CTE 可以被优化器优化，在一定程度上提高查询效率。
3. 连接与过滤：在连接 students 表和 scores 表时，使用 JOIN 条件关联 student_id，并在 WHERE 子句中过滤出课程为“数学”的记录，减少不必要的数据处理。
4. 最终筛选：在主查询中，通过 WHERE rank <= 3 筛选出每个班级数学成绩排名前三的学生信息，避免返回过多无用数据。