表：
任務：id（bigint）名稱（varchar）。保存任務詳細資訊
job : id (varchar(UUID)) task_id (bigint (id of class)), staus (varchar(50)), created_time (time stamp)。保持任務執行細節
狀態的可能值為 FAIL/COMPLETED/INTERRUPTED
我想要實作的是從作業表中獲取每項任務的所有最新值
如果任務不存在作業，則回傳狀態為 null
SELECT
p.id, j.status
FROM
tas p
inner JOIN
job j ON j.task_id = p.id
inner JOIN
job j1 ON j.task_id = j1.task_id and j.create_time > j1.create_time;
uj5u.com熱心網友回復：
對于支持您的 SQL 版本，ROW_NUMBER()您可以執行以下操作：
WITH info as(
SELECT
p.id,
j.status,
ROW_NUMBER() OVER(PARTITION BY p.id ORDER BY j.created_time DESC) AS rn
FROM tas p
LEFT JOIN job j ON j.task_id = p.id
)
SELECT id, status
FROM info
WHERE rn = 1
否則，只需使用 cte 或子查詢。
SELECT p.id, t.status
FROM tas AS p
LEFT JOIN (
SELECT task_id, MAX(created_time) as created_time
FROM job
GROUP BY task_id
) as lt
ON p.id = lt.task_id
LEFT JOIN task AS t ON lt.task_id = t.task_id AND lt.created_time = t.created_time
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/483430.html
下一篇：如何從adf中的全域引數傳遞表名