mysql - 将 LEFT JOIN 子查询限制为 1 个结果

Question

下面的查询似乎限制了 所有 的结果，当它被 LEFT JOINed 时，所以子查询中的总数仅为 1。我怎样才能让它 LIMIT 以便我得到一个 1 :1 projects 行与存储在 projects_hours_archive 中的最后日期之间的匹配每周一次的 cron 作业？

projected_hours_archive 包含列:id、project_id、hours 和 datetime。

SELECT
    GROUP_CONCAT( projected_hours_last.date, '|', projected_hours_last.number ) AS 'projected_last_info'
FROM
projects


LEFT JOIN (
    SELECT *
    FROM
    projected_hours_archive
    ORDER BY date DESC
    LIMIT 1
) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id )

WHERE projected_hours > 0

GROUP BY projects.id

我尝试采用 MySQL Limit LEFT JOIN Subquery after joining但没有成功。如果我删除子查询中的 LIMIT，我会得到太多结果。

Answer 1

在子查询中使用 group by 并获取每个项目的最大日期。

编辑:根据 OP 评论，添加第二个最大日期。

这个技巧来自 mysql how to get 2nd highest value with group by and in a left join被使用。

SELECT
    GROUP_CONCAT( projected_hours_last.secondMaxDate, '|', projected_hours_last.number ) AS 'projected_last_info'
FROM
projects


LEFT JOIN (
    SELECT project_id, max(date) as maxDate,
           substring_index(substring_index(group_concat(date order by date desc), ',', 2), ',', -1
                            ) as secondMaxDate
    FROM
    projected_hours_archive
    group by project_id
) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id )

WHERE projected_hours > 0

GROUP BY projects.id