聊天系统未读消息计数SQL查询修正求助

看起来你的问题出在原SQL的未读消息统计逻辑上——原查询里的COUNT(ATEN.mensagem)统计了该客户所有发送给你的消息（包括已读），而且因为和ut_compras做了LEFT JOIN，当一个客户有多条购买记录时，会导致消息被重复计数，最终结果自然不准。

下面给你两种修正方案，你可以根据自己的需求选择：

方案一：使用子查询直接统计（简洁直观） #

这种方式直接针对每个客户单独统计未读消息，避免关联表带来的重复计数问题：

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, 
    -- 获取该客户发送给你的最新消息内容
    (SELECT mensagem 
     FROM ut_atendimentos 
     WHERE id_usuario_envio = CLI.id AND id_usuario_recebido = 59163 
     ORDER BY data_mensagem DESC LIMIT 1) AS mensagem,
    -- 统计未读消息：只计数未被标记为已读的消息
    (SELECT COUNT(*) 
     FROM ut_atendimentos 
     WHERE id_usuario_envio = CLI.id AND id_usuario_recebido = 59163 
       AND mensagem_visualizada = 0) AS novas_mensagens,
    -- 统计总消费（如果客户没有购买记录会返回NULL，可加COALESCE转成0）
    COALESCE(SUM(COMP.valor), 0) AS valor_total, 
    -- 获取最新的购买时间
    (SELECT data 
     FROM ut_compras 
     WHERE id_cliente = CLI.id 
     ORDER BY data DESC LIMIT 1) AS ultima_compra, 
    ARQ.nome AS foto,
    -- 获取最新消息的时间
    (SELECT data_mensagem 
     FROM ut_atendimentos 
     WHERE id_usuario_envio = CLI.id AND id_usuario_recebido = 59163 
     ORDER BY data_mensagem DESC LIMIT 1) AS data_mensagem,
    -- 最新消息的未读状态
    (SELECT mensagem_visualizada 
     FROM ut_atendimentos 
     WHERE id_usuario_envio = CLI.id AND id_usuario_recebido = 59163 
     ORDER BY data_mensagem DESC LIMIT 1) AS mensagem_visualizada
FROM ut_clientes AS CLI 
LEFT JOIN ut_compras AS COMP ON COMP.id_cliente = CLI.id 
LEFT JOIN ut_arquivos AS ARQ ON ARQ.id_tipo = CLI.id AND ARQ.tipo = 'ut_clientes'
-- 可选：如果只想显示有过消息的客户，保留这个WHERE；如果要显示所有客户，去掉它
WHERE EXISTS(
    SELECT 1 FROM ut_atendimentos 
    WHERE id_usuario_envio = CLI.id AND id_usuario_recebido = 59163
)
GROUP BY 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, ARQ.nome
ORDER BY data_mensagem DESC;

关键修正点： #

1. 未读消息统计：用独立子查询过滤mensagem_visualizada = 0（请根据你数据表的实际值调整，比如如果是布尔型就用= false），直接统计该客户所有未读消息，不会受其他关联表影响。
2. 避免重复计数：不再通过LEFT JOIN关联ut_atendimentos后分组，而是用子查询获取最新消息，彻底解决多购买记录导致的消息重复统计问题。
3. 处理NULL值：用COALESCE把没有购买记录的客户总消费转成0，避免显示NULL。

方案二：使用CTE（更清晰，适合复杂查询） #

如果你的查询后续还要扩展，用CTE（公共表表达式）可以让逻辑更模块化，可读性更强：

WITH cliente_mensagem_resumo AS (
    -- 先统计每个客户的未读消息数和最新消息时间
    SELECT 
        id_usuario_envio,
        COUNT(CASE WHEN mensagem_visualizada = 0 THEN 1 END) AS novas_mensagens,
        MAX(data_mensagem) AS ultima_mensagem_data
    FROM ut_atendimentos
    WHERE id_usuario_recebido = 59163
    GROUP BY id_usuario_envio
),
ultima_mensagem_detalhe AS (
    -- 根据最新消息时间，获取对应的消息内容和状态
    SELECT 
        ATEN.id_usuario_envio,
        ATEN.mensagem,
        ATEN.data_mensagem,
        ATEN.mensagem_visualizada
    FROM ut_atendimentos ATEN
    JOIN cliente_mensagem_resumo CM ON ATEN.id_usuario_envio = CM.id_usuario_envio 
                                   AND ATEN.data_mensagem = CM.ultima_mensagem_data
    WHERE ATEN.id_usuario_recebido = 59163
),
cliente_compra_resumo AS (
    -- 统计每个客户的总消费和最新购买时间
    SELECT 
        id_cliente,
        SUM(valor) AS valor_total,
        MAX(data) AS ultima_compra_data
    FROM ut_compras
    GROUP BY id_cliente
)
-- 主查询关联所有统计结果
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, 
    UM.mensagem,
    COALESCE(CM.novas_mensagens, 0) AS novas_mensagens,
    COALESCE(CC.valor_total, 0) AS valor_total, 
    CC.ultima_compra_data AS ultima_compra, 
    ARQ.nome AS foto,
    UM.data_mensagem,
    UM.mensagem_visualizada
FROM ut_clientes AS CLI 
LEFT JOIN cliente_mensagem_resumo CM ON CLI.id = CM.id_usuario_envio
LEFT JOIN ultima_mensagem_detalhe UM ON CLI.id = UM.id_usuario_envio
LEFT JOIN cliente_compra_resumo CC ON CLI.id = CC.id_cliente
LEFT JOIN ut_arquivos AS ARQ ON ARQ.id_tipo = CLI.id AND ARQ.tipo = 'ut_clientes'
-- 按最新消息时间排序，没有消息的客户排在最后
ORDER BY UM.data_mensagem DESC NULLS LAST;

额外建议： #

• 请确认ut_atendimentos表中mensagem_visualizada的字段值，确保过滤条件和实际未读状态匹配。
• 如果你的数据量较大，建议给ut_atendimentos表建立联合索引：CREATE INDEX idx_atendimentos_envio_recebido ON ut_atendimentos(id_usuario_recebido, id_usuario_envio);，能大幅提升查询速度。
• 测试时可以单独运行未读消息统计的子查询，比如SELECT COUNT(*) FROM ut_atendimentos WHERE id_usuario_envio = [某个客户ID] AND id_usuario_recebido = 59163 AND mensagem_visualizada = 0;，验证计数是否正确。

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