如何用SQL获取每个好友的最后一条聊天消息？

没问题，我们来搞定这个需求！你现在的SQL已经能拿到所有和当前用户（ID=4）相关的对话消息，但还需要筛选出每个好友的最新一条，包括处理message_date相同的情况（用id来兜底排序）。

核心思路 #

我们需要对每个好友的对话消息分组，每组内按message_date降序排序，日期相同时按message.id降序排序（因为id是自增主键，更大的ID代表更晚创建的消息），然后取每组的第一条记录。

完整SQL方案（支持现代数据库：MySQL 8+、PostgreSQL等） #

用窗口函数ROW_NUMBER()是最清晰直观的方式：

WITH conversation_messages AS (
    SELECT 
        m.id,
        m.message, 
        m.message_read, 
        m.message_date, 
        CASE WHEN m.sender = 4 THEN m.receiver ELSE m.sender END as friend_id, 
        CASE WHEN m.sender = 4 THEN p2.nickname ELSE p1.nickname END as name, 
        CASE WHEN m.sender = 4 THEN p2.image ELSE p1.image END as image 
    FROM message as m 
    JOIN profile as p1 ON m.sender = p1.user_id 
    JOIN profile as p2 ON m.receiver = p2.user_id 
    WHERE 4 IN (m.sender, m.receiver)
)
SELECT message, message_read, message_date, friend_id, name, image
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY friend_id ORDER BY message_date DESC, id DESC) as rn
    FROM conversation_messages
) as ranked_messages
WHERE rn = 1;

逻辑拆解 #

1. CTE conversation_messages：先完成你原来的查询逻辑，把所有和用户4相关的消息整理好，计算出对应的friend_id、昵称和头像。
2. 窗口函数分组排序：用ROW_NUMBER()给每个friend_id下的消息编号，排序规则是message_date从新到旧，日期相同则按id从大到小，这样每组里最新的消息会被标记为rn=1。
3. 筛选结果：只保留rn=1的记录，就是每个好友的最后一条消息。

兼容旧版本数据库（比如MySQL 5.x） #

如果你的数据库不支持CTE，可以把逻辑合并到子查询里：

SELECT message, message_read, message_date, friend_id, name, image
FROM (
    SELECT 
        m.id,
        m.message, 
        m.message_read, 
        m.message_date, 
        CASE WHEN m.sender = 4 THEN m.receiver ELSE m.sender END as friend_id, 
        CASE WHEN m.sender = 4 THEN p2.nickname ELSE p1.nickname END as name, 
        CASE WHEN m.sender = 4 THEN p2.image ELSE p1.image END as image,
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN m.sender = 4 THEN m.receiver ELSE m.sender END ORDER BY message_date DESC, id DESC) as rn
    FROM message as m 
    JOIN profile as p1 ON m.sender = p1.user_id 
    JOIN profile as p2 ON m.receiver = p2.user_id 
    WHERE 4 IN (m.sender, m.receiver)
) as ranked_messages
WHERE rn = 1;

验证结果 #

用你提供的测试数据，这个查询会返回你期望的结果：

+-----------+--------------+---------------------+-----------+-------+-------+
| message   | message_read | message_date        | friend_id | name  | image |
+-----------+--------------+---------------------+-----------+-------+-------+
| SUP MATE  | 1            | 2018-05-15 11:04:24 | 1         | JUAN  | NULL  |
| heha      | 1            | 2018-05-15 10:36:11 | 2         | user3 | NULL  |
+-----------+--------------+---------------------+-----------+-------+-------+

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