MYSQL LEFT JOINing另一个表中的最新记录

Question

我有一个training_stats表(当前应有的训练)，还有一个completed_training表。

我想要做的是使用完成的表中的最后完成日期查询到期培训。

我几乎得到了我想要的，我得到了应有的培训，但它们与每个已完成的记录重复(因为每个当前到期的记录有许多已完成的记录)，并且我只想要单行和最新完成日期.

我一直在尝试使用MAX，当我独立运行MAX查询时，我得到了最后一条记录。但是当 MAX 查询处于联接中时，它会返回所有已完成的行。

这是我正在使用的查询:

SELECT s.course_stat_id
      ,o.org_name
      ,u.id
      ,u.first_name
      ,u.last_name
      ,a.area_id
      ,a.area_name
      ,tc.course_id
      ,tc.course_name
      ,s.assigned_on
      ,s.due
      ,s.pass_mark
      ,s.completed_on
      ,completed.complete_training_id
      ,completed.complete_date
FROM training_stats s
JOIN organisations o ON o.org_id = s.org_id
LEFT JOIN (
    SELECT complete_training_id
          ,user_id
          ,area_id
          ,course_id
          ,max(completed_on) AS complete_date
    FROM completed_training
    GROUP BY complete_training_id
) completed ON completed.user_id = s.user_id
AND completed.area_id = s.area_id
AND completed.course_id = s.course_id
LEFT JOIN users u ON u.id = s.user_id
LEFT JOIN areas a ON a.area_id = s.area_id
LEFT JOIN training_courses tc ON tc.course_id = s.course_id
WHERE u.active = 1
AND o.active = 1
AND s.assigned = 1

你能看出我做错了什么吗？

Answer 1

与您的预期结果并不完全一致，但失败可能是您的 group by 和 JOIN 造成的。您的分组依据仅在培训 ID 上，但您还拉取用户、区域和类(class)以及所述相应培训 ID、用户、区域、类(class)的最大完成日期。您的分组依据和加入应符合独特的特征。

在没有看到数据的情况下，我解释的查询是“complete_training_id”是该表的自动增量列。话虽如此，该 ID 永远只会有一条记录。

话虽如此，完整的培训表可以包含单个用户、区域和类(class)的多个培训日，其中您想要最近的培训日。例如，某人正在上大学，需要参加许多计算机类(class)，并且他们是之前的复习人员，因此假设所有类(class) ID 都是相同的。一个人可以参加 2012 年、2014 年、2016 年。您可能希望用户/区域/类(class)的实例显示 2016 年日期的培训。那么让我们先看一下。

select
      ct.user_id,
      ct.area_id,
      ct.course_id,
      max(ct.completed_on) AS complete_date
   FROM 
      completed_training ct
   GROUP BY 
      ct.user_id,
      ct.area_id,
      ct.course_id

现在，对于每个用户、领域和学习类(class)，我都有一条包含最近完成日期的记录。现在让我们提取其余的详细信息，但由于您也需要完整的训练 ID，因此我在下面的查询中应用了 MAX()。默认情况下，每次添加新记录时，ID 都会增加，因此一年前完成的 ID 的值将低于今天完成的 ID。因此，您可以获得给定用户、区域、类(class)的完整 ID 及其相应日期。

SELECT
      s.course_stat_id,
      o.org_name,
      u.id,
      u.first_name,
      u.last_name,
      a.area_id,
      a.area_name,
      tc.course_id,
      tc.course_name,
      s.assigned_on,
      s.due,
      s.pass_mark,
      s.completed_on,
      ct.complete_training_id,
      ct.complete_date
   FROM 
      training_stats s
         JOIN organisations o 
            ON s.org_id = o.org_id 
           AND o.active = 1
         LEFT JOIN 
            ( select
                    ct.user_id,
                    ct.area_id,
                    ct.course_id,
                    max(ct.complete_training_id ) as complete_training_id,
                    max(ct.completed_on) AS complete_date
                 FROM 
                    completed_training ct
                 GROUP BY 
                    ct.user_id,
                    ct.area_id,
                    ct.course_id ) ct
            on s.user_id = ct.user_id
            AND s.area_id = ct.area_id
            AND s.course_id = ct.course_id
         JOIN users u 
            ON s.user_id = u.id
            AND u.active = 1
         LEFT JOIN areas a 
            ON s.area_id = a.area_id
         LEFT JOIN training_courses tc 
            ON s.course_id = tc.course_id
   WHERE
      s.assigned = 1