MS Access SQL查询优化：按DateIntervalID保留单条最新隐私培训记录

我们用MS Access管理400多名员工的培训证书：

• 部分员工需在两年周期（以DateIntervalID区分）内完成80小时培训
• 普通培训同一周期内不可重复参加，但隐私培训这类公司强制培训允许重复
• 需要实现：查询隐私培训记录时，每个员工的每个DateIntervalID下仅保留最新的一条记录

原SQL无法满足需求，返回了同一DateIntervalID下的多条隐私培训记录，现寻求修正方案。

原SQL代码 #

SELECT
    [Training Extended (Test)].Section, 
    [Training Extended (Test)].[Employee Name], 
    [Training Extended (Test)].[Training Course], 
    [Training Extended (Test)].Hours, 
    [Training Extended (Test)].Category, 
    [Training Extended (Test)].DateIntervalID, 
    [Training Extended (Test)].[Training Start Date] 
FROM
    [Training Extended (Test)]
WHERE 
    ([Training Extended (Test)].[Training Course] = "Privacy Training") 
    AND ([Training Extended (Test)].[Training Start Date] IN 
        (SELECT MAX([Training Extended (Test)].[Training Start Date]) 
         FROM [Training Extended (Test)] 
         GROUP BY [Training Extended (Test)].[Employee Name], [Training Extended (Test)].DateIntervalID, [Training Extended (Test)].[Training Start Date]))
GROUP BY 
    [Training Extended (Test)].Section, 
    [Training Extended (Test)].[Employee Name], 
    [Training Extended (Test)].[Training Course], 
    [Training Extended (Test)].Hours, 
    [Training Extended (Test)].Category, 
    [Training Extended (Test)].DateIntervalID, 
    [Training Extended (Test)].TrainingID, 
    [Training Extended (Test)].[Employee ID], 
    [Training Extended (Test)].[Training Start Date]
HAVING 
    (MAX([Training Extended (Test)].[Training Start Date])) BETWEEN #7/1/2016# AND DATE()

错误返回结果 #

Section Employee Name   Training Course       Hours Category    DateIntervalID  Training Start Date
---------------------------------------------------------------------------------------------------
Rancho  John Michael    Privacy Training    1   Mandatory Training  3   7/11/21
Rancho  John Michael    Privacy Training    1   Mandatory Training  3   6/15/22
Rancho  John Michael    Privacy Training    1   Mandatory Training  4   11/15/22
Rancho  John Michael    Privacy Training    1   Mandatory Training  4   11/14/23

期望返回结果 #

Section Employee Name   Training Course Hours   Category    DateIntervalID  Training Start Date
---------------------------------------------------------------------------------------------------
Rancho  John Michael    Privacy Training    1   Mandatory Training  3   6/15/22
Rancho  John Michael    Privacy Training    1   Mandatory Training  4   11/14/23

修正方案 #

问题分析 #

原SQL的核心错误：

1. 子查询的GROUP BY中多余加入了Training Start Date，导致每个日期单独分组，MAX()无法筛选出同一周期的最新日期
2. 主查询的GROUP BY和HAVING属于冗余操作，反而会干扰结果筛选

修正后的SQL #

SELECT
    t.Section,
    t.[Employee Name],
    t.[Training Course],
    t.Hours,
    t.Category,
    t.DateIntervalID,
    t.[Training Start Date]
FROM
    [Training Extended (Test)] AS t
INNER JOIN (
    -- 子查询：获取每个员工+每个周期的最新隐私培训日期
    SELECT
        [Employee Name],
        DateIntervalID,
        MAX([Training Start Date]) AS LatestTrainingDate
    FROM
        [Training Extended (Test)]
    WHERE
        [Training Course] = "Privacy Training"
        AND [Training Start Date] BETWEEN #7/1/2016# AND DATE()
    GROUP BY
        [Employee Name],
        DateIntervalID
) AS sub ON 
    t.[Employee Name] = sub.[Employee Name]
    AND t.DateIntervalID = sub.DateIntervalID
    AND t.[Training Start Date] = sub.LatestTrainingDate
WHERE
    t.[Training Course] = "Privacy Training"

逻辑说明 #

1. 子查询先筛选出隐私培训记录，按员工姓名+周期ID分组，计算每组的最新培训日期
2. 主查询通过INNER JOIN关联原表，匹配员工、周期ID和最新日期，从而得到每个周期下的最新记录
3. 日期过滤直接放在子查询中，减少不必要的数据关联

内容的提问来源于stack exchange，提问作者Theo Dillard III