MySQL中SELECT与子查询应用：获取客户最新消息问题

嘿，我完全懂你的困扰！你想要按客户ID分组，拿到每个客户最新的那条data_mensagem消息，但现在跑出来的却是最早插入的那条，这确实挺让人挠头的。

为什么你的原SQL会返回最早的消息？ #

你的原SQL直接把ut_atendimentos和客户表关联后就GROUP BY CLI.id，但数据库在这种情况下（没有指定聚合逻辑时），会从每个客户的所有消息记录里随机选一条返回——大多数时候会取存储在最前面的那条，也就是最早插入的消息，这就和你的需求背道而驰了。

解决方案1：用子查询先找每个客户的最新消息时间 #

我们可以先通过子查询找出每个客户对应的最新消息时间，再用这个时间去关联回ut_atendimentos，就能精准拿到对应的最新消息内容：

SELECT 
    CLI.id, 
    CLI.nome, 
    CLI.senha, 
    CLI.email, 
    CLI.cpf, 
    CLI.celular, 
    CLI.data_nasc, 
    CLI.genero, 
    CLI.data_cadastro, 
    CLI.status, 
    CLI.id_socket, 
    ATEN.mensagem, 
    ARQ.nome AS foto, 
    ATEN.data_mensagem 
FROM ut_clientes AS CLI 
LEFT JOIN ut_arquivos AS ARQ ON (ARQ.id_tipo = CLI.id AND ARQ.tipo = "ut_clientes")
-- 第一步：子查询找出每个客户的最新消息时间
INNER JOIN (
    SELECT id_usuario_envio, MAX(data_mensagem) AS ultima_data
    FROM ut_atendimentos
    WHERE id_usuario_envio != 59163
    GROUP BY id_usuario_envio
) AS ATEN_LAST ON CLI.id = ATEN_LAST.id_usuario_envio
-- 第二步：用客户ID+最新时间关联，拿到对应的消息
INNER JOIN ut_atendimentos AS ATEN 
    ON ATEN.id_usuario_envio = ATEN_LAST.id_usuario_envio 
    AND ATEN.data_mensagem = ATEN_LAST.ultima_data
WHERE CLI.id != 59163
ORDER BY ATEN.data_mensagem DESC

解决方案2：用自增主键更精准（如果有的话） #

如果ut_atendimentos表有自增主键（比如id），用主键的最大值来关联会更准确——毕竟如果同一客户在同一时间发送了多条消息，用时间可能会拿到多条，而主键唯一对应一条记录：

SELECT 
    CLI.id, 
    CLI.nome, 
    CLI.senha, 
    CLI.email, 
    CLI.cpf, 
    CLI.celular, 
    CLI.data_nasc, 
    CLI.genero, 
    CLI.data_cadastro, 
    CLI.status, 
    CLI.id_socket, 
    ATEN.mensagem, 
    ARQ.nome AS foto, 
    ATEN.data_mensagem 
FROM ut_clientes AS CLI 
LEFT JOIN ut_arquivos AS ARQ ON (ARQ.id_tipo = CLI.id AND ARQ.tipo = "ut_clientes")
INNER JOIN (
    SELECT id_usuario_envio, MAX(id) AS ultima_id
    FROM ut_atendimentos
    WHERE id_usuario_envio != 59163
    GROUP BY id_usuario_envio
) AS ATEN_LAST ON CLI.id = ATEN_LAST.id_usuario_envio
INNER JOIN ut_atendimentos AS ATEN ON ATEN.id = ATEN_LAST.ultima_id
WHERE CLI.id != 59163
ORDER BY ATEN.data_mensagem DESC

解决方案3：用窗口函数（适合MySQL 8.0+或支持窗口函数的数据库） #

如果你的数据库支持窗口函数（比如MySQL 8.0、PostgreSQL等），这个方法会更直观，不需要多次关联：

SELECT 
    CLI.id, 
    CLI.nome, 
    CLI.senha, 
    CLI.email, 
    CLI.cpf, 
    CLI.celular, 
    CLI.data_nasc, 
    CLI.genero, 
    CLI.data_cadastro, 
    CLI.status, 
    CLI.id_socket, 
    ATEN.mensagem, 
    ARQ.nome AS foto, 
    ATEN.data_mensagem 
FROM ut_clientes AS CLI 
LEFT JOIN ut_arquivos AS ARQ ON (ARQ.id_tipo = CLI.id AND ARQ.tipo = "ut_clientes")
INNER JOIN (
    SELECT 
        id_usuario_envio, 
        mensagem, 
        data_mensagem,
        -- 按客户分组，消息时间倒序编号，最新的消息编号为1
        ROW_NUMBER() OVER (PARTITION BY id_usuario_envio ORDER BY data_mensagem DESC) AS rn
    FROM ut_atendimentos
    WHERE id_usuario_envio != 59163
) AS ATEN ON CLI.id = ATEN.id_usuario_envio AND ATEN.rn = 1
WHERE CLI.id != 59163
ORDER BY ATEN.data_mensagem DESC

这里的ROW_NUMBER()函数会给每个客户的消息按时间倒序排号，我们只取编号为1的那条，就是最新的消息啦。

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