面试题：MySQL WHERE子句专家难度题目

Question

假设有三个表，`students`表（包含`student_id`、`student_name`等字段），`courses`表（包含`course_id`、`course_name`等字段），`enrollments`表（连接学生与课程，包含`student_id`、`course_id`、`enrollment_date`等字段）。现在要查询出选修了‘数学’课程，并且在选修该课程的所有学生中成绩排名在前20%的学生姓名及成绩（假设`enrollments`表中有`score`字段表示成绩），请写出完整的SQL查询语句，要求考虑性能优化。

Answer 1

WITH RankedScores AS (
    SELECT 
        s.student_name,
        e.score,
        -- 计算排名
        ROW_NUMBER() OVER (PARTITION BY c.course_name ORDER BY e.score DESC) AS score_rank,
        COUNT(*) OVER (PARTITION BY c.course_name) AS total_students
    FROM 
        students s
    JOIN 
        enrollments e ON s.student_id = e.student_id
    JOIN 
        courses c ON e.course_id = c.course_id
    WHERE 
        c.course_name = '数学'
)
SELECT 
    student_name,
    score
FROM 
    RankedScores
WHERE 
    score_rank <= CEIL(total_students * 0.2);

性能优化说明

1. 使用窗口函数：通过窗口函数 ROW_NUMBER() 和 COUNT(*) 来计算每个学生在所选课程中的排名以及选修该课程的学生总数，避免了多次扫描表。
2. CTE（公用表表达式）：使用CTE RankedScores 提高查询的可读性和可维护性，同时允许在后续的 SELECT 语句中直接引用计算结果。
3. 过滤条件前置：在连接表之前，通过 WHERE c.course_name = '数学' 先对 courses 表进行过滤，减少参与连接的数据量，从而提高查询性能。