PostgreSQL按指定列去重查询：获取用户各频道最新消息

问题说明 #

在PostgreSQL中有一张chat表，表结构及数据如下：

需要查询指定user_id下，每个channel_id对应的最新消息，期望结果如下：

可行查询语句 #

方法1：窗口函数ROW_NUMBER()（推荐） #

通过窗口函数按频道分组，对每组内的消息按创建日期倒序排序，取排序后的第一条记录：

SELECT channel_id, message, chat_created_date
FROM (
    SELECT 
        channel_id, 
        message, 
        chat_created_date,
        ROW_NUMBER() OVER (PARTITION BY channel_id ORDER BY chat_created_date DESC) AS rn
    FROM chat
    WHERE user_id = 23 -- 替换为目标user_id
) AS sub_query
WHERE rn = 1;

注：如果同一频道存在多条同一最新日期的消息，该方法只会返回其中一条（按数据库默认排序取第一条）。

方法2：窗口函数RANK() #

若需要保留同一频道下所有最新日期的消息，将ROW_NUMBER()替换为RANK()即可：

SELECT channel_id, message, chat_created_date
FROM (
    SELECT 
        channel_id, 
        message, 
        chat_created_date,
        RANK() OVER (PARTITION BY channel_id ORDER BY chat_created_date DESC) AS rn
    FROM chat
    WHERE user_id = 23
) AS sub_query
WHERE rn = 1;

方法3：关联子查询 #

先找出每个频道的最新消息日期，再关联原表获取对应消息：

SELECT c.channel_id, c.message, c.chat_created_date
FROM chat c
INNER JOIN (
    SELECT channel_id, MAX(chat_created_date) AS latest_date
    FROM chat
    WHERE user_id = 23
    GROUP BY channel_id
) AS latest_dates 
    ON c.channel_id = latest_dates.channel_id 
    AND c.chat_created_date = latest_dates.latest_date
WHERE c.user_id = 23;

注：该方法同样会返回同一频道下所有最新日期的消息。

内容的提问来源于stack exchange，提问作者back-end