关联聚合函数条件引用超作用域表值的SQL问题求助

报错原因 #

你写的SQL报错是因为内层子查询无法直接引用外层[Student Enrollment]（别名A）的ENROLL_DT字段——子查询的作用域仅覆盖自身内部，访问不到外层表的列。

可用的正确SQL写法 #

方法1：窗口函数（推荐，性能更优） #

先用ROW_NUMBER()窗口函数筛选出所有激活状态的学籍记录，再关联选课表，匹配每个选课日期前最新的学籍：

WITH ActiveTracks AS (
    SELECT 
        STUDENT,
        TRACK,
        DECLARE_DT,
        -- 按学生分组，申报日期倒序排序，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY STUDENT ORDER BY DECLARE_DT DESC) AS rn
    FROM [Student Track]
    WHERE STATUS = 'Active'
),
EnrollmentMatches AS (
    SELECT 
        A.STUDENT,
        A.COURSE,
        A.ENROLL_DT,
        B.TRACK,
        -- 标记选课日期前的最新学籍记录
        ROW_NUMBER() OVER (PARTITION BY A.STUDENT, A.COURSE ORDER BY B.DECLARE_DT DESC) AS rn
    FROM [Student Enrollment] AS A
    LEFT JOIN ActiveTracks AS B
        ON A.STUDENT = B.STUDENT
        AND B.DECLARE_DT <= A.ENROLL_DT
)
SELECT STUDENT, COURSE, ENROLL_DT, TRACK
FROM EnrollmentMatches
WHERE rn = 1;

方法2：关联子查询 #

直接在SELECT语句中用子查询，为每条选课记录匹配符合条件的最新学籍：

SELECT 
    A.STUDENT,
    A.COURSE,
    A.ENROLL_DT,
    (SELECT TOP 1 TRACK
     FROM [Student Track] AS B
     WHERE B.STUDENT = A.STUDENT
       AND B.STATUS = 'Active'
       AND B.DECLARE_DT <= A.ENROLL_DT
     ORDER BY B.DECLARE_DT DESC) AS TRACK
FROM [Student Enrollment] AS A;

注：PostgreSQL需将TOP 1替换为LIMIT 1；MySQL 8.0+也支持此写法。

方法3：LATERAL JOIN（适配SQL Server、PostgreSQL等） #

通过LATERAL JOIN为每条选课记录单独匹配最新的符合条件的学籍：

SELECT 
    A.STUDENT,
    A.COURSE,
    A.ENROLL_DT,
    B.TRACK
FROM [Student Enrollment] AS A
LEFT JOIN LATERAL (
    SELECT TRACK
    FROM [Student Track]
    WHERE STUDENT = A.STUDENT
      AND STATUS = 'Active'
      AND DECLARE_DT <= A.ENROLL_DT
    ORDER BY DECLARE_DT DESC
    LIMIT 1 -- SQL Server替换为TOP 1
) AS B ON 1=1;

验证结果 #

以上三种写法均可输出预期结果：

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