MSSQL分区窗口函数搜索方法：查询学生指定时间范围内课程通过情况

1. 分区内执行查找操作的可行性 #

你提到的在LEAD()对应的PARTITION分区内做范围搜索是可以实现的，但不需要局限于LEAD()函数本身的逻辑，直接用*窗口函数的范围帧（RANGE frame）*就能完成同一学生分区内、指定时间范围内的课程匹配统计，不需要额外拉取其他字段再做判断。
你可以在原有包含LEAD()的查询逻辑里，新增一个窗口聚合列，直接统计第一门课结课后1年内的及格课程数量，示例逻辑如下：

WITH ranked_student_course AS (
  SELECT
    student_id,
    course_id,
    course_end_date,
    is_passed,
    -- 对学生及格课程按结课时间排序
    ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY course_end_date) AS course_rn,
    -- 原有逻辑：取下一门连续课程的结课时间
    LEAD(course_end_date,1) OVER(PARTITION BY student_id ORDER BY course_end_date) AS next_course_end_date,
    -- 新增逻辑：统计当前课程结课后1年内的其他及格课程数
    COUNT(course_id) OVER(
      PARTITION BY student_id
      ORDER BY course_end_date
      RANGE BETWEEN INTERVAL '1 day' FOLLOWING AND INTERVAL '1 year' FOLLOWING
    ) AS year_1_pass_count
  FROM student_course
  WHERE is_passed = 1
)
SELECT
  student_id,
  course_id first_course_id,
  course_end_date first_course_end_date,
  CASE
    WHEN next_course_end_date <= course_end_date + INTERVAL '1 year' THEN '1年内通过紧接的第二门课程'
    WHEN year_1_pass_count > 0 THEN '1年内未通过第二门课程，但通过了其他课程'
    ELSE '1年内未通过任何后续课程'
  END AS check_result
FROM ranked_student_course
WHERE course_rn = 1;

以上逻辑只需要一次表扫描即可完成所有规则判断，不需要额外做表关联。

2. EXISTS关联方案的优劣对比 #

这个方案是否更优取决于你的业务输出需求：

• 如果只需要筛选出「第一门课结课后1年内有通过任意课程」的记录，不需要输出判断分类、后续课程明细等信息，WHERE EXISTS方案效率更高：它属于半连接逻辑，匹配到符合条件的行就会终止搜索，不需要对全部分区数据做聚合计算，在学生课程数据量较大的场景下执行成本更低，示例写法如下：

  SELECT
    sc1.student_id,
    sc1.course_id first_course_id,
    sc1.course_end_date first_course_end_date
  FROM student_course sc1
  WHERE sc1.is_passed = 1
  -- 筛选学生的第一门及格课程
  AND NOT EXISTS(
    SELECT 1 FROM student_course sc0
    WHERE sc0.student_id = sc1.student_id
    AND sc0.is_passed = 1
    AND sc0.course_end_date < sc1.course_end_date
  )
  -- 判断1年内是否存在其他及格课程
  AND EXISTS(
    SELECT 1 FROM student_course sc2
    WHERE sc2.student_id = sc1.student_id
    AND sc2.is_passed = 1
    AND sc2.course_end_date > sc1.course_end_date
    AND sc2.course_end_date <= sc1.course_end_date + INTERVAL '1 year'
  )
  

• 如果你需要在结果中同时输出多种判断结果、后续课程数量等信息，窗口函数方案更简洁，避免了多次子查询关联的冗余逻辑。

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