我有一個作業正常的查詢，但是我在其中使用了兩次相同的子查詢。我想知道是否有辦法重用這個子查詢。這是查詢：
SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
WHERE homework_messages.message_type = 'submit'
AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
AND (
(
SELECT MAX(homework_messages.created_at) FROM homework_messages
WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
) < homework_messages.created_at
OR
(
SELECT MAX(homework_messages.created_at) FROM homework_messages
WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
) IS NULL
)
GROUP BY homeworks.id
如您所見，我將這些行重復了兩次：
(
SELECT MAX(homework_messages.created_at) FROM homework_messages
WHERE homework_messages.homework_id = homeworks.id AND homework_messages.message_type != 'submit'
)
我嘗試使用 WITH 子句，但它似乎不適合這個問題，或者我使用錯誤。無論如何感謝您的幫助。
uj5u.com熱心網友回復：
這是一個cross join lateral建議。我沒有深入研究你的業務邏輯，只是試圖讓它保持等價。
SELECT DISTINCT homeworks.*
FROM homeworks
INNER JOIN homework_messages ON homeworks.id = homework_messages.homework_id
cross join lateral ( -- your subquery follows
SELECT MAX(created_at) as created_at FROM homework_messages
WHERE homework_id = homeworks.id AND message_type != 'submit'
) as lat
WHERE homework_messages.message_type = 'submit'
AND homework_messages.created_at::date <= (CURRENT_DATE - '5 days'::interval)
AND (lat.created_at < homework_messages.created_at OR lat.created_at IS NULL)
GROUP BY homeworks.id;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/391452.html
標籤：sql PostgreSQL的 子查询