直接打开

如果您使用 DISTINCT ON ，您需要一个子查询：

DISTINCT ON

If you use DISTINCT ON, you need a subquery for that:

SELECT *
FROM  (
   SELECT DISTINCT ON (conversation_id) *
   FROM   message t
   ORDER  BY conversation_id, created_at DESC
   ) sub
ORDER BY created_at DESC;

子查询中的顺序必须与 DISTINCT ON 子句，因此必须将其包装在外部查询中才能达到所需的排序顺序。

The order in the subquery must agree with the columns in the DISTINCT ON clause, so you must wrap it in an outer query to arrive at your desired sort order.

类似的故事，您还需要一个子查询：

Similar story, you need a subquery as well:

SELECT id, sender_id, receiver_id, conversation_id, subject, body, created_at
FROM  (
   SELECT *, row_number() OVER (PARTITION BY conversation_id
                                ORDER BY created_at DESC) AS rn
   FROM   message t
   ) sub
WHERE  rn = 1
ORDER  BY created_at DESC;

也可能较慢。