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

Question

以下查询似乎将所有结果都限制在LEFT JOIN中，因此子查询中的总数只有1。我该如何进行LIMIT，以便在projects行和projects_hours_archive中存储的最后日期之间获得1:1匹配的结果，projects_hours_archive是一个定期每周收集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

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

- meder omuraliev

Answer 1

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

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

这个MySQL如何通过group by和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

- radar

---

抱歉，我忘记放进去了 - 我正在尝试获取倒数第二个日期。 - meder omuraliev

@meder，这篇文章中有一个技巧可以获取第二个值，https://dev59.com/GnTYa4cB1Zd3GeqPxrPa。由于数据不在那里，因此更新了答案，无法尝试。 - radar

Answer 2

我有同样的问题。

我猜测你的字段projects.project_id是唯一的（因此该表中没有重复项）。否则，您可以像以前一样使用DISTINCT(projects.project_id)或GROUP BY projects.project_id。

解决方法＃1（在连接时使用GROUP BY）：

        SELECT  a1.project_id,
                GROUP_CONCAT(a2.date, '|', a2.number ) AS 'projected_last_info'
        FROM projects a1
        LEFT JOIN (
            SELECT b1.project_id, b1.date, b1.number
            FROM projected_hours_archive b1 
            ORDER BY b1.date DESC
            GROUP BY b1.project_id
        ) a2 ON a2.project_id = a1.project_id
        WHERE a1.projected_hours > 0

解决方案 #2（直接在SELECT语句的子查询中使用LIMIT）：

        SELECT  a1.project_id,
                (SELECT GROUP_CONCAT(a2.date, '|', a2.number )
                    FROM projected_hours_archive b1
                    WHERE b1.project_id = a1.project_id
                    ORDER BY b1.date DESC
                ) AS 'projected_last_info'
        FROM projects a1
        WHERE a1.projected_hours > 0

- Leonid Dashko