如何筛选Jack（a_id=2）参与的所有房间的最后聊天消息？

你的核心问题是没有先筛选出Jack参与的房间范围，直接对全表使用distinct on导致结果包含了Jack未参与的房间。以下两种方法都能解决问题，包括你提到的partition窗口函数方案：

方法一：改进distinct on写法（PostgreSQL专属） #

先通过CTE获取Jack所有参与的房间，再在这些房间内取最新消息：

WITH jack_rooms AS (
    -- 先拿到Jack参与过的所有房间ID
    SELECT DISTINCT r_id FROM chat WHERE a_id = 2
)
SELECT DISTINCT ON (c.r_id) c.*
FROM chat c
JOIN jack_rooms jr ON c.r_id = jr.r_id
ORDER BY c.r_id, c.date DESC;

执行后只会返回r_id=77的最新消息，符合你的需求。

方法二：使用窗口函数PARTITION BY（标准SQL，兼容性更强） #

用ROW_NUMBER()给每个房间的消息按日期倒序排名，再筛选排名第一的记录：

WITH ranked_messages AS (
    SELECT 
        *,
        -- 按房间分组，日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY r_id ORDER BY date DESC) AS rn
    FROM chat
    -- 仅保留Jack参与的房间
    WHERE r_id IN (SELECT DISTINCT r_id FROM chat WHERE a_id = 2)
)
SELECT a_id, name, r_id, message, date
FROM ranked_messages
WHERE rn = 1;

为什么之前的写法出错？ #

你之前的SQL没有添加房间过滤条件，distinct on (r_id)会遍历全表的所有房间，自然会包含Jack从未参与的r_id=79房间的最新消息。只要先限定房间范围，两种写法都能得到正确结果。

内容的提问来源于stack exchange，提问作者qubicswap.com