如何编写SQL查询获取用户聊天会话及对应最后一条消息？

针对你的需求，这里提供两种通用的SQL写法，适配不同数据库环境：

方法一：使用窗口函数（适配MySQL 8.0+、PostgreSQL、SQL Server等） #

窗口函数可以轻松给每个会话的消息按时间排序，筛选出每条会话的最后一条消息：

SELECT 
    c.id, c.name, c.host, c.client, c.closed, c.attach,
    m.response, m.message, m.time
FROM chats c
LEFT JOIN (
    SELECT 
        chat_id, response, message, time,
        ROW_NUMBER() OVER (PARTITION BY chat_id ORDER BY time DESC) AS rn
    FROM messages
) m ON c.id = m.chat_id AND m.rn = 1
WHERE c.client = ? OR c.host = ?
ORDER BY COALESCE(m.time, c.created_time) DESC; -- 如果会话无消息，可按会话创建时间排序，需替换成实际字段

• 子查询里用ROW_NUMBER()按chat_id分组，每条消息按time倒序排，排名1的就是该会话最后一条消息
• 用LEFT JOIN确保即使会话还没有消息，也能返回会话本身（不需要的话可换回INNER JOIN）

方法二：使用子查询获取最大时间（适配所有数据库版本） #

如果你的数据库不支持窗口函数，这种兼容性更强的写法也能实现需求：

SELECT 
    c.id, c.name, c.host, c.client, c.closed, c.attach,
    m.response, m.message, m.time
FROM chats c
LEFT JOIN messages m 
    ON c.id = m.chat_id 
    AND m.time = (
        SELECT MAX(time) 
        FROM messages 
        WHERE chat_id = c.id
    )
WHERE c.client = ? OR c.host = ?
ORDER BY COALESCE(m.time, c.created_time) DESC;

• 子查询先找出每个会话的最新消息时间，再关联messages表匹配对应消息
• 同样用LEFT JOIN保留无消息的会话，按需调整

注意事项 #

• 替换c.created_time为chats表中实际的会话创建时间字段（如果有的话），用于无消息会话的排序
• 如果要确保只返回有消息的会话，把LEFT JOIN改成INNER JOIN即可

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