特定用户消息列表按created_at排序、去重及LEFT JOIN用户表的技术问询

嘿，我来帮你搞定这个SQL需求！你要的是给消息列表按时间排序、每个收件人只留一条，还要关联用户表拿姓名，这其实是个很常见的「分组取最值+关联查询」场景，我给你两种常用的实现方式，适配不同的数据库版本：

方法一：用窗口函数（推荐，支持MySQL 8+、PostgreSQL、SQL Server等） #

窗口函数是最清晰直观的写法，能轻松给每个收件人的消息按时间排名，然后筛选出你要的那一条（默认取最新的，要最早的话改个排序方向就行）：

WITH ranked_messages AS (
    SELECT
        m.recipient_id,
        m.send_time,
        m.message_content, -- 替换成你实际需要的消息字段
        -- 按收件人分组，发送时间倒序排，最新的消息排第1
        ROW_NUMBER() OVER (PARTITION BY m.recipient_id ORDER BY m.send_time DESC) AS rn
    FROM messages m
)
SELECT
    rm.recipient_id,
    u.user_name,
    rm.send_time,
    rm.message_content
FROM ranked_messages rm
LEFT JOIN users u ON rm.recipient_id = u.user_id
WHERE rn = 1 -- 只保留每个收件人的最新一条消息
ORDER BY rm.send_time DESC; -- 最终按发送时间排序

逻辑说明：

1. 先用WITH子句生成一个临时表，给每个收件人的消息按发送时间倒序编号，最新的消息编号为1
2. 左连接用户表，通过收件人ID关联拿到对应的姓名
3. 过滤出编号为1的记录，就是每个收件人的最新消息，最后再按发送时间排序

方法二：子查询分组取最值（适配MySQL 5.x等不支持窗口函数的版本） #

如果你的数据库版本比较旧，不支持窗口函数，可以用子查询先找出每个收件人的最新消息时间，再匹配主表数据：

SELECT
    m.recipient_id,
    u.user_name,
    m.send_time,
    m.message_content -- 替换成你实际需要的消息字段
FROM messages m
LEFT JOIN users u ON m.recipient_id = u.user_id
WHERE (m.recipient_id, m.send_time) IN (
    SELECT recipient_id, MAX(send_time)
    FROM messages
    GROUP BY recipient_id
)
ORDER BY m.send_time DESC;

逻辑说明：

1. 子查询按收件人ID分组，取出每个收件人的最大发送时间（也就是最新消息的时间）
2. 主查询匹配收件人ID和对应的最大时间，拿到对应的消息记录
3. 左连接用户表获取姓名，最后按发送时间排序

小提示： #

• 如果要保留每个收件人最早的消息，只需要把方法一里的ORDER BY m.send_time DESC改成ASC，方法二里的MAX(send_time)改成MIN(send_time)就行
• 记得把代码里的表名（messages、users）和字段名（message_content等）替换成你实际使用的名称

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