Microsoft SQL Server中静态表与动态表按日期全外连接方案咨询

在Microsoft SQL Server环境中，我有两张表：

• 动态表（LiveTable）：每日更新，存储学生考勤的每日快照，同一学生按日期有多条记录，字段包括StudentID、Date、Attendance、Grade。
• 静态表（StaticTable）：存储学期首日的学生注册信息，每个学生仅一条唯一记录，字段包括StudentID、Grade。

需要实现每日数据比对，找出同时存在于两张表的学生、仅在动态表的学生、仅在静态表的学生。计划使用全外连接，但不清楚如何处理动态表的日期维度，需满足以下要求：

1. 每日检查静态表中的学生是否存在于动态表；
2. 每日检查动态表中的学生是否存在于静态表；
3. 静态表中的学生需出现在所有记录日期中；
4. 非静态表的学生仅需出现在其动态表有记录的日期。

示例数据 #

静态表（StaticTable） #

动态表（LiveTable） #

预期结果 #

解决方案 #

SQL 实现代码 #

-- 提取动态表中所有不重复的日期
WITH AllDates AS (
    SELECT DISTINCT Date 
    FROM LiveTable
),
-- 生成静态表学生与所有日期的组合，确保静态学生每个日期都有记录
StaticStudentsAllDates AS (
    SELECT 
        s.StudentID,
        ad.Date,
        s.Grade
    FROM StaticTable s
    CROSS JOIN AllDates ad
)
-- 全外连接静态学生日期组合和动态表，计算标识字段
SELECT 
    COALESCE(ssad.StudentID, lt.StudentID) AS StudentID,
    COALESCE(ssad.Date, lt.Date) AS Date,
    CASE WHEN ssad.StudentID IS NOT NULL THEN 1 ELSE 0 END AS InStaticTable,
    CASE WHEN lt.StudentID IS NOT NULL THEN 1 ELSE 0 END AS InLiveTable,
    lt.Attendance,
    COALESCE(lt.Grade, ssad.Grade) AS Grade
FROM StaticStudentsAllDates ssad
FULL OUTER JOIN LiveTable lt
    ON ssad.StudentID = lt.StudentID
    AND ssad.Date = lt.Date
ORDER BY StudentID, Date;

代码逻辑说明 #

1. AllDates：先获取动态表中所有存在的日期，确保覆盖所有需要检查的日期范围。
2. StaticStudentsAllDates：通过CROSS JOIN将静态表的每个学生与所有日期组合，满足“静态学生出现在所有记录日期”的要求。
3. 全外连接：将静态学生日期组合与动态表按StudentID+Date匹配：
   • 静态学生在某日期无动态记录时，动态表字段为NULL，InLiveTable标记为0；
   • 动态表中的非静态学生只会出现在他们有记录的日期，自动满足“非静态学生仅出现在有记录日期”的要求；
   • 使用COALESCE合并Grade字段，优先取动态表的最新值，无动态记录时用静态表的初始值。

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