如何查询每个对话的最后一条消息？PostgreSQL实现咨询

你的原查询其实已经实现了获取每个对话最后一条消息的需求——DISTINCT ON会保留由LEAST(from_id, to_id), GREATEST(from_id, to_id)定义的每个对话分组中，排序后的第一条记录。而你指定了created_at DESC，意味着每个对话里最新的消息会排在分组的最前面，所以DISTINCT ON会选中这条最新消息。如果你的结果不符合预期，建议检查数据或排序逻辑是否有误。

如果需要换用其他写法，以下是几种常用方案：

方案1：优化DISTINCT ON的可读性 #

给分组字段起别名，让逻辑更清晰：

SELECT DISTINCT ON (conversation_pair)
       id, message, created_at, from_id, to_id
FROM (
    SELECT 
        id, message, created_at, from_id, to_id,
        (LEAST(from_id, to_id), GREATEST(from_id, to_id)) AS conversation_pair
    FROM messages
) AS sub_query
ORDER BY conversation_pair, created_at DESC;

方案2：使用窗口函数ROW_NUMBER() #

这是更通用且易读的写法，适合复杂业务场景：

SELECT id, message, created_at, from_id, to_id
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(from_id, to_id), GREATEST(from_id, to_id)
            ORDER BY created_at DESC, id DESC -- 若同一时间有多条消息，按id倒序确保取最新插入的
        ) AS row_num
    FROM messages
) AS sub_query
WHERE row_num = 1;

这里PARTITION BY按对话分组，ROW_NUMBER()给每个分组内的记录按created_at倒序编号，编号为1的就是该对话的最后一条消息。如果同一对话存在同一时间的多条消息，加上id DESC可以确保取到最后插入的那条（因为id是自增主键）。

方案3：分组关联查询 #

通过分组找到每个对话的最新消息时间，再关联原表获取完整消息：

SELECT m.id, m.message, m.created_at, m.from_id, m.to_id
FROM messages m
INNER JOIN (
    SELECT 
        LEAST(from_id, to_id) AS user_a,
        GREATEST(from_id, to_id) AS user_b,
        MAX(created_at) AS last_created_time
    FROM messages
    GROUP BY user_a, user_b
) AS last_msg_info 
ON (LEAST(m.from_id, m.to_id) = last_msg_info.user_a 
    AND GREATEST(m.from_id, m.to_id) = last_msg_info.user_b
    AND m.created_at = last_msg_info.last_created_time);

注意：如果同一对话中存在同一时间的多条消息，这个查询会返回所有这些消息，而前两种方案只会返回其中一条。

内容的提问来源于stack exchange，提问作者user672009