如何修改SQL查询以展示参训人员每月最后培训日期

要实现按参训人员、月份展示最后一次培训日期的需求，你需要用MAX()聚合函数替代SUM()，核心是提取每个用户对应月份内的最大培训日期。以下是修改后的完整SQL代码：

SELECT
    "t4"."Attendee_Name__Last__First_Middle_Suffix_" AS "Attendee_Name__Last__First_Middle_Suffix__res",
    "t4"."datetime_months_Class_Sessions_Session_Start_Date" AS "datetime_months_Class_Sessions_Session_Start_Date_res",
    MAX("t4"."Session_Start_Date") AS "last_training_date_res" -- 替换SUM为MAX，获取当月最后一次培训日期
    -- 若需要保留每月培训时长统计，可保留下面这行
    -- SUM("t4"."Hours") AS "sum__Hours_res"
FROM
    (SELECT
         "t3"."Attendee_Name__Last__First_Middle_Suffix_",
         DATE_TRUNC('MONTH', "t2"."Session_Start_Date") AS "datetime_months_Class_Sessions_Session_Start_Date",
         "t0"."Hours",
         "t2"."Session_Start_Date" -- 新增：保留原始培训日期用于取最大值
     FROM
         (SELECT
              "_CLASSSESSIONID_PART1",
              "_CLASSSESSIONID_PART2",
              "Hours",
              "Credit Name" AS "Credit_Name"
          FROM
              "PM_CLASSES"."Class Session Credits"
          WHERE
              "Credit Name" = 'FAR 139 CATEGORY 1 - AIRPORT FAMILIARIZATION') AS "t0"
     INNER JOIN 
         ((SELECT
               "_CLASSSESSIONID_PART1",
               "_CLASSSESSIONID_PART2",
               "Session Start Date" AS "Session_Start_Date"
           FROM
               "PM_CLASSES"."Class Sessions"
           WHERE
               "Session Start Date" >= '2023-04-10 00:00:00'
               AND "Session Start Date" < '2024-04-04 00:00:00') AS "t2" 
     INNER JOIN 
         (SELECT
              "_CLASSSESSIONID_PART1",
              "_CLASSSESSIONID_PART2",
              "Attendee Name (Last, First Middle Suffix)" AS "Attendee_Name__Last__First_Middle_Suffix_"
          FROM
              "PM_CLASSES"."Class Session Attendees") AS "t3" ON "t2"."_CLASSSESSIONID_PART1" = "t3"."_CLASSSESSIONID_PART1"
        AND "t2"."_CLASSSESSIONID_PART2" = "t3"."_CLASSSESSIONID_PART2"
      ) ON "t0"."_CLASSSESSIONID_PART1" = "t2"."_CLASSSESSIONID_PART1"
      AND "t0"."_CLASSSESSIONID_PART2" = "t2"."_CLASSSESSIONID_PART2"
    ) AS "t4"
GROUP BY
    "t4"."Attendee_Name__Last__First_Middle_Suffix_",
    "t4"."datetime_months_Class_Sessions_Session_Start_Date"
ORDER BY
    "t4"."Attendee_Name__Last__First_Middle_Suffix_",
    "t4"."datetime_months_Class_Sessions_Session_Start_Date"

关键修改说明 #

1. 内层查询新增字段：在t4的SELECT列表中加入t2"."Session_Start_Date，保留原始的培训日期，用于后续聚合取最大值。
2. 替换聚合函数：将外层的SUM("t4"."Hours")替换为MAX("t4"."Session_Start_Date")，这样就能得到每个参训人员在对应月份的最后一次培训日期。
3. 可选保留时长统计：如果需要同时展示每月培训时长，只需取消注释代码中的SUM行即可，GROUP BY无需额外调整。

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