在我的项目中,我有一个tasks
(id,名称)表。我还有一个status_updates
(id、task_id、user_id、状态created_at)表。
我想选择其最新status_update等于特定状态的任务。
但是,我不知道如何组合以下事情:
- 仅对每个任务使用最新的status_update。
- 过滤掉状态不正确的status_updates。
- 将其包装在内部联接中,以便最终结果是任务集合。
使用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上工作的答案。
使用子查询:
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