获取指定用户参与的所有chat中该用户最新发送消息的SQL编写问题

问题分析 #

• 第一个SQL的问题：子查询是全局筛选用户6的所有消息后取最新的1条，整个子查询最终只有1条结果，只能匹配到对应chatid的关联行，其他chat自然匹配不到，返回null。
• 第二个SQL的问题：SELECT MAX(created_at) from chat_message取的是整张chat_message表的全局最大时间，不是每个chat下用户6的最大时间，所以只有在这个全局最大时间有发消息的chat能匹配到内容，其他chat都会返回null。

正确写法 #

写法1：窗口函数（兼容MySQL 8.0+、PostgreSQL、SQL Server等主流新版数据库） #

SELECT c.chatid, m.message, m.userid
FROM chat AS c
INNER JOIN chat_user AS cu ON cu.chatid = c.chatid
LEFT JOIN (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY chatid ORDER BY created_at DESC) AS rn
    FROM chat_message
    WHERE userid = 6
) AS m ON m.chatid = c.chatid AND m.rn = 1
WHERE cu.userid = 6;

子查询里给用户6的所有消息按chatid分组，每个分组内按创建时间倒序排序，rn=1的就是每个chat下最新的那条消息，关联的时候只取rn=1的行，就能拿到每个chat对应的最新消息。如果不需要展示用户没发过消息的chat，把LEFT JOIN改成INNER JOIN即可自动过滤null行。

写法2：关联子查询（兼容旧版本MySQL等不支持窗口函数的场景） #

SELECT c.chatid, m.message, m.userid
FROM chat AS c
INNER JOIN chat_user AS cu ON cu.chatid = c.chatid
LEFT JOIN chat_message AS m 
  ON m.chatid = c.chatid 
  AND m.userid = 6
  AND m.created_at = (
      SELECT MAX(created_at) 
      FROM chat_message 
      WHERE chatid = c.chatid AND userid = 6
  )
WHERE cu.userid = 6
GROUP BY c.chatid, m.message, m.userid;

内层子查询关联外层的c.chatid，取当前chat下用户6的最大创建时间，这样就能匹配到每个chat下用户最新发的那条消息。如果要过滤掉用户没有发过消息的chat，加AND m.message IS NOT NULL条件即可。

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