mysql join 子查询结果,mysql – 将LEFT JOIN子查询限制为1个结果

下面的查询似乎在LEFT JOINed时限制所有结果,因此子查询中的总数只有1.我如何使它成为LIMIT,以便在项目行和project_hours_archive中存储的最后日期之间得到1：1的匹配它存储了每周一次在cron作业上收集的projects.projected_hours记录？

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

解决方法:

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

编辑：根据OP评论,添加第二个最大日期.

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

标签：mysql,sql

来源： https://codeday.me/bug/20190609/1206771.html