mysql - 如何使用左连接获取所有行并获取第二个表中的最新行

Question

我正在尝试按照说明 in this answer 进行操作如何获取连接表中的最新行。

我有两张 table 。

Projects : id, title
Status : project_id, status_id, created(DATETIME)

当我知道项目 ID(示例 = 2)时，我就有了正确的查询来选择最新的状态更新。

SELECT projects. * , project_state_project_map.status_id AS status, 
project_state_project_map.created AS status_created
FROM projects
LEFT JOIN (

    SELECT * 
    FROM project_state_project_map
    WHERE project_id = 2
    ORDER BY created DESC 
    LIMIT 1
    )
project_state_project_map ON project_state_project_map.project_id = projects.id
WHERE projects.id = 2
LIMIT 1

但是，我无法弄清楚如何选择当前状态的所有项目。我必须对 sql 进行哪些更改才能获取所有项目的最新状态。

Answer 1

我建议更改您的查询以使用聚合函数来获取最新日期和状态:

SELECT p. *, 
  pm1.status_id AS status, 
  pm1.created AS status_created
FROM projects p
LEFT JOIN project_state_project_map pm1
  ON pm1.project_id = p.id
INNER JOIN 
(
  SELECT max(created) MaxDate, project_id
  FROM project_state_project_map
  WHERE project_id = 2
  GROUP BY project_id
) pm2 
  ON pm1.project_id = pm2.project_id
  AND pm1.created = pm2.MaxDate
WHERE p.id = 2

这会获取每个项目的最大(创建) 日期，然后此结果用于返回该日期的状态。

这也可以写成:

SELECT p. *, 
  pm.status_id AS status, 
  pm.created AS status_created
FROM projects p
LEFT JOIN
(
  SELECT pm1.project_id,
    pm1.status_id,
    pm1.created
  FROM project_state_project_map pm1
  INNER JOIN 
  (
    SELECT max(created) MaxDate, project_id
    FROM project_state_project_map
    WHERE project_id = 2
    GROUP BY project_id
  ) pm2
    ON pm1.project_id = pm2.project_id
    AND pm1.created = pm2.MaxDate
) pm
  ON pm.project_id = p.id
WHERE p.id = 2;