IM聊天消息列表SQL

   WITH latest_messages AS (
            SELECT
                id,
                content,
                create_time,
                LEAST( from_user_id, to_user_id ) AS session_user_id,
                GREATEST( from_user_id, to_user_id ) AS opposite_user_id,
                CASE

                    WHEN from_user_id = #{userId} THEN
                        'OUTGOING' ELSE 'INCOMING'
                    END AS message_direction,
                ROW_NUMBER() OVER ( PARTITION BY LEAST( from_user_id, to_user_id ), GREATEST( from_user_id, to_user_id ) ORDER BY create_time DESC ) AS rn
            FROM
                offline_messages
            WHERE
                #{userId} IN ( from_user_id, to_user_id )
        ) SELECT
              lm.id,
              lm.content,
              lm.create_time,
              lm.session_user_id,
              lm.opposite_user_id,
              lm.message_direction,
              u.avatar AS opposite_avatar_url,
              u.nickname AS opposite_nickname
        FROM
            latest_messages lm
                JOIN member_user u ON lm.opposite_user_id = u.id
        WHERE
            lm.rn = 1
        ORDER BY
            lm.create_time DESC;

实现功能：获取当前登录用户接收到的其他用户发送给他的最新消息，保存会话列表，以实现它发送给别人消息不会返回多条数据，连表查询对方头像，昵称