在postgresql中重用子查询

Question

我有一个运行良好的查询，但是我在其中使用了两次相同的子查询。我想知道是否有一种方法可以重用这个子查询。以下是查询：

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子句，它似乎不适合这个问题，或者我可能用错了。不管怎样，谢谢你的帮助。

Answer 1

这是一个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;