查询未完成培训要求：关联表仅取最新完成记录

需求说明 #

需要编写SQL查询返回当前用户未完成的培训要求，涉及4个核心表：

• TrainingRequirements：存储用户需完成的课程，核心字段包含CourseId、UserId、Active
• UserTraining：存储用户培训完成记录，核心字段包含CourseId、UserId、DateCompleted
• User：存储用户基础信息，核心字段Active
• Course：存储课程信息，核心字段MonthsValid（培训有效月数）

现有问题 #

当前编写的查询能返回用户从未参加过培训的情况，但会额外带出大量旧的培训记录（比如年度培训的历史完成记录），而我们只需要对比每个用户每门课程的最新完成记录来判断是否过期。

现有查询代码：

SELECT * 
FROM TrainingRequirements
    INNER JOIN Users on Users.Id = TrainingRequirements.UserId
    INNER JOIN Courses on Courses.Id = TrainingRequirements.CourseId
    LEFT OUTER JOIN UserTrainings on UserTrainings.CourseId = TrainingRequirements.CourseId 
    AND UserTrainings.UserId = TrainingRequirements.UserId
WHERE 
    Users.Active = 1 AND 
    TrainingRequirements.Active = 1 AND 
    (UserTrainings.Id is null OR 
       -- 处理从未参加培训的情况
    GETDATE() >= DATEADD(month, Courses.MonthsValid, UserTrainings.DateCompleted)) 
       -- 处理培训已过期需重学的情况

示例数据 #

TrainingRequirements表 #

Id | UserId  | CourseId | active
1  | 1       |1          | 1
2  | 1       |2          | 1
3  | 1       |3          | 0 
4  | 1       |4          | 1

UserTrainings表 #

Id | UserId | CourseId | DateCompleted
1  |1       |1         | 2022-10-23 12:20:11.526
2  |1       |2         | 2021-11-15 05:01:12.320
3  |1       |2         | 2020-12-11 11:11:40.320

Courses表 #

Id | Name                  | MonthsValid
1  | 'Cool Training'       | 36
2  | 'Secret Training'     | 12
3  | 'Top Secret Training' | 6
4  | 'Unpopular Training'  | 12

期望结果 #

仅返回未完成或已过期的培训要求，排除旧的有效记录：

TrainingRequirementId | UserId | CourseId | LastTaken | Expired
2                     | 1      | 2        | 11/15/2021| 11/15/2022
4                     | 1      | 4        | null      | null

优化后的查询方案 #

核心思路是先通过子查询获取每个用户每门课程的最新培训完成记录，再将结果与主表关联，筛选未完成或已过期的情况：

SELECT
    tr.Id AS TrainingRequirementId,
    tr.UserId,
    tr.CourseId,
    CONVERT(VARCHAR(10), ut.LastTaken, 101) AS LastTaken,
    CONVERT(VARCHAR(10), DATEADD(month, c.MonthsValid, ut.LastTaken), 101) AS Expired
FROM TrainingRequirements tr
INNER JOIN Users u ON u.Id = tr.UserId
INNER JOIN Courses c ON c.Id = tr.CourseId
LEFT JOIN (
    -- 子查询：获取每个用户每门课的最新完成日期
    SELECT
        UserId,
        CourseId,
        MAX(DateCompleted) AS LastTaken
    FROM UserTrainings
    GROUP BY UserId, CourseId
) ut ON ut.UserId = tr.UserId AND ut.CourseId = tr.CourseId
WHERE
    u.Active = 1
    AND tr.Active = 1
    -- 筛选条件：从未参加 或 最新培训已过期
    AND (ut.LastTaken IS NULL OR GETDATE() >= DATEADD(month, c.MonthsValid, ut.LastTaken))

代码说明 #

1. 子查询通过GROUP BY UserId, CourseId和MAX(DateCompleted)提取每个用户每门课的最新完成记录，彻底排除旧记录干扰
2. 主查询用LEFT JOIN关联子查询，保证从未参加培训的记录能正常返回
3. 最终筛选仅针对最新记录判断是否过期，同时保留未参加的情况
4. 使用CONVERT函数将日期格式化为示例中的MM/DD/YYYY样式，可根据需求调整格式码

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