SQL三表JOIN查询：获取用户聊天列表并关联最新消息实现问题

通用兼容版本（支持MySQL 5.x、PostgreSQL等绝大多数数据库） #

$queryGetChats = '
SELECT 
    t1.chatId, 
    t1.firstUserId, 
    t1.secondUserId, 
    t2.userNickname AS firstUserNickname, 
    t3.userNickname AS secondUserNickname,
    t4.messageToWalletId,
    t4.readIndicator
FROM chat AS t1
LEFT JOIN users AS t2 ON t1.firstUserId = t2.userId
LEFT JOIN users AS t3 ON t1.secondUserId = t3.userId
-- 子查询聚合获取每个会话的最新消息ID
LEFT JOIN (
    SELECT chatId, MAX(messageId) AS latestMsgId 
    FROM messages 
    GROUP BY chatId
) AS t_max ON t1.chatId = t_max.chatId
-- 关联消息表拿到最新消息的对应字段
LEFT JOIN messages AS t4 ON t_max.latestMsgId = t4.messageId
WHERE t1.firstUserId = ? OR t1.secondUserId = ? 
ORDER BY t1.chatId DESC;
';
$paramsChats = array($userId, $userId);

return Db::queryAll($queryGetChats, $paramsChats);

注：如果需要过滤掉没有任何消息的会话，把关联messages的LEFT JOIN改为INNER JOIN即可；无消息的会话默认返回的messageToWalletId、readIndicator字段为NULL。

窗口函数版本（仅支持MySQL 8.0+、PostgreSQL 9.4+等支持窗口函数的数据库） #

$queryGetChats = '
SELECT 
    t1.chatId, 
    t1.firstUserId, 
    t1.secondUserId, 
    t2.userNickname AS firstUserNickname, 
    t3.userNickname AS secondUserNickname,
    t4.messageToWalletId,
    t4.readIndicator
FROM chat AS t1
LEFT JOIN users AS t2 ON t1.firstUserId = t2.userId
LEFT JOIN users AS t3 ON t1.secondUserId = t3.userId
LEFT JOIN (
    SELECT 
        chatId, 
        messageToWalletId, 
        readIndicator,
        ROW_NUMBER() OVER (PARTITION BY chatId ORDER BY messageId DESC) AS rn
    FROM messages
) AS t4 ON t1.chatId = t4.chatId AND t4.rn = 1
WHERE t1.firstUserId = ? OR t1.secondUserId = ? 
ORDER BY t1.chatId DESC;
';
$paramsChats = array($userId, $userId);

return Db::queryAll($queryGetChats, $paramsChats);

内容的提问来源于stack exchange，提问作者Robert Michálek