mysql - SQL按has_many关系的最新元素的列选择

Question

在我的项目中，我有一个tasks(id, name) 表。我还有一个 status_updates(id, task_id, user_id, status, created_at) 表。

我想选择最新 status_update 等于特定状态的任务。

但是，我不知道如何组合以下内容:

1. 仅对每个任务使用最新的 status_update。
2. 过滤掉状态不正确的 status_updates。
3. 将其包装在 INNER JOIN 中，因此最终结果是一组任务。

当使用 GROUP_BY 时，似乎 1. 和 2. 相互抵消了。一个例子:

SELECT * FROM status_updates GROUP BY task_id ORDER created_at DESC

仅返回每个任务的最新 status_update。然而，这:

SELECT * FROM status_updates WHERE status='1' GROUP BY task_id ORDER created_at DESC

在 ORDER 之前执行 WHERE，从而返回状态正确的最新的，而不是从最新的，只有状态正确的那些 .

我也尝试过使用 HAVING，但由于它只适用于聚合列，我不知道如何正确使用它，或者它在这种情况下是否有帮助。

我正在使用 Ruby on Rails，所以我想要一个既能在 MySQL 上工作又能在 SQLite 上工作的答案。

Answer 1

使用子查询:

SELECT
    T.id,
    T.name,
    SU.status
FROM
    Tasks T
INNER JOIN (SELECT task_id, MAX(created_at) AS max_created_at FROM Status_Updates GROUP BY task_id) SQ ON SQ.task_id = T.id
INNER JOIN Status_Updates SU ON
    SU.task_id = SQ.task_id AND
    SU.created_at = SQ.max_created_at
WHERE
    SU.status = '1'

使用窗口函数和 CTE(虽然您的 RDBMS 目前不支持它们，但很多人支持它们并且将来可能支持)。这个的优点是它可以更好地处理联系:

WITH MyCTE AS
(
    SELECT
        T.id,
        T.name,
        SU.status,
        ROW_NUMBER OVER(PARTITION BY T.id ORDER BY SU.created_at DESC, id DESC) AS row_num
    FROM
        Tasks T
    INNER JOIN Status_Updates SU ON SU.task_id = T.id
)
SELECT
    id,
    name,
    status
FROM
    MyCTE
WHERE
    row_num = 1