仅传入一个地址参数获取聊天应用会话列表的MySQL查询如何编写

你的需求可以用MySQL 8.0支持的窗口函数单条SQL实现，完整查询如下：

-- 入参：当前用户地址
SET @current_user = 'CMKovgETx2oYxhoYKAeSakmipBjbmQ9ZHF';

WITH session_msg AS (
    SELECT 
        sendAddr,
        receiveAddr,
        lastMessage,
        `read`,
        text,
        -- 生成会话唯一标识，无视收发方向避免同一会话拆分
        CONCAT(LEAST(sendAddr, receiveAddr), '|', GREATEST(sendAddr, receiveAddr)) AS session_id,
        -- 识别会话中的对方参与者
        IF(sendAddr = @current_user, receiveAddr, sendAddr) AS otherParticipant
    FROM campus.messages
    WHERE sendAddr = @current_user OR receiveAddr = @current_user
),
ranked_msg AS (
    SELECT 
        @current_user AS user,
        otherParticipant,
        lastMessage,
        text,
        -- 仅统计对方发送给当前用户的未读消息，和示例输出完全匹配
        SUM(IF(receiveAddr = @current_user AND `read`=0, 1, 0)) OVER (PARTITION BY session_id) AS unread,
        -- 同会话内按消息时间倒序排序，第一位为最新消息
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY lastMessage DESC) AS rn
    FROM session_msg
)
SELECT user, otherParticipant, unread, lastMessage, text
FROM ranked_msg
WHERE rn = 1
ORDER BY lastMessage DESC;

逻辑说明 #

• 第一层CTE session_msg 先过滤所有和当前用户相关的消息，同时生成唯一会话ID和对方参与者标识，解决同一会话因收发方向不同被拆分的问题
• 第二层CTE ranked_msg 用窗口函数实现两个核心逻辑：
  1. 按会话分组统计未读消息数，如果需要统计会话内所有未读，去掉SUM判断里的receiveAddr = @current_user条件即可
  2. 给同一会话的消息按发送时间倒序排序，排名第一的就是会话的最新消息
• 最终筛选rn=1的行，就是每个会话的最新汇总数据，完全匹配你的预期输出要求

内容的提问来源于stack exchange，提问作者Michal Žídek