SQL Server基于course_code列实现按课程维度分页查询方案

实现方案及性能说明 #

你编写的CTE查询逻辑本身是符合需求的，两处添加相同WHERE子句不会降低查询效率，反而会缩小数据扫描范围，提升查询性能。

逻辑优化建议

原CTE中没有去重，同一个course_code对应多行数据时，会导致FETCH NEXT 25 ROWS实际取到的不同课程数量少于25，建议在CTE中增加DISTINCT去重：

WITH cte AS 
(
    SELECT DISTINCT course_code
    FROM courses
    WHERE course_date <= '2021-12-31'
    ORDER BY course_code
    OFFSET 0 ROWS
    FETCH NEXT 25 ROWS ONLY
)
SELECT * 
FROM courses
INNER JOIN cte ON cte.course_code = courses.course_code
WHERE course_date <= '2021-12-31'

性能说明

数据库查询优化器会自动识别重复的过滤条件，不会重复执行过滤逻辑，两处相同的日期筛选只会提前过滤掉不符合条件的无效数据，减少后续查询的计算量。如果为courses表建立(course_date, course_code)联合索引，该查询的执行效率会更高：CTE可以直接通过索引快速拿到25个不重复的课程编码，关联查询阶段也可以通过索引快速匹配到对应课程的所有关联行。

替代实现方案

也可以通过窗口函数DENSE_RANK实现相同需求，写法更简洁，性能和CTE方案差异不大：

SELECT course_code, course_date, participant_name
FROM (
    SELECT 
        *,
        DENSE_RANK() OVER (ORDER BY course_code) AS course_rank
    FROM courses
    WHERE course_date <= '2021-12-31'
) t
WHERE course_rank <= 25

该方案的逻辑是给同一course_code的所有行分配相同的排名，直接筛选排名前25的所有行即可。

内容的提问来源于stack exchange，提问作者Erwan PLB