为什么我的 SQL COUNT(*) 聚合所有行而不是按 ID 和海报分组？

*SQL COUNT() 错误聚合行：常见陷阱**

SQL 查询中的一个常见挑战涉及 COUNT(*) 聚合函数意外地计算所有行而不是执行预期的分组。 这通常源于 GROUP BY 子句的错误放置或遗漏。

让我们检查一个有问题的查询及其解决方案：

原始查询旨在根据“Aura”状态对行进行计数，并按“Poster”和“ID”分组：

SELECT `ID`, `To`, `Poster`, `Content`, `Time`, ifnull(`Aura`,0) as `Aura`
FROM (
    SELECT * FROM (
        SELECT DISTINCT * FROM messages m
        INNER JOIN
        (
            SELECT Friend2 as Friend FROM friends WHERE Friend1 = '1'
            UNION ALL
            SELECT Friend1 as Friend FROM friends WHERE Friend2 = '1'
        ) friends ON m.Poster = friends.`Friend`
        UNION ALL SELECT DISTINCT *, '1' FROM messages where `Poster`='1'
    ) var
    LEFT JOIN
    (
        select `ID` as `AuraID`, `Status` as `AuraStatus`, count(*) as `Aura`
            from messages_aura
    ) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)
) final

GROUP BY `ID`, `Poster`
ORDER BY `Time` DESC LIMIT 10

未达到预期结果，即每个“海报”和“ID”组合的“Aura”出现次数（例如，ID 1、海报 2 具有 2 个 Aura 实例）。 子查询中的 COUNT(*) 函数错误地聚合了 messages_aura.

中的所有行

解决方案：正确分组GROUP BY

问题在于与 GROUP BY 连接的子查询中缺少 messages_aura 子句。更正后的查询是：

SELECT `ID`, `To`, `Poster`, `Content`, `Time`, ifnull(`Aura`,0) as `Aura`
FROM (
    SELECT * FROM (
        SELECT DISTINCT * FROM messages m
        INNER JOIN
        (
            SELECT Friend2 as Friend FROM friends WHERE Friend1 = '1'
            UNION ALL
            SELECT Friend1 as Friend FROM friends WHERE Friend2 = '1'
        ) friends ON m.Poster = friends.`Friend`
        UNION ALL SELECT DISTINCT *, '1' FROM messages where `Poster`='1'
    ) var
    LEFT JOIN
    (
        select `ID` as `AuraID`, `Status` as `AuraStatus`, count(*) as `Aura`
        from messages_aura
        GROUP BY AuraID, AuraStatus  -- The crucial addition
    ) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)
) final

GROUP BY `ID`, `Poster`
ORDER BY `Time` DESC LIMIT 10

通过将 GROUP BY AuraID, AuraStatus 添加到内部 SELECT 语句，COUNT(*) 函数现在可以正确计算 AuraID 和 AuraStatus 的每个唯一组合的行数，从而生成所需的分组结果。 这可确保 Aura 在行级别准确计数。 然后，外部 GROUP BY 子句根据 ID 和 Poster 进一步聚合结果。

以上是为什么我的 SQL COUNT(*) 聚合所有行而不是按 ID 和海报分组？的详细内容。更多信息请关注PHP中文网其他相关文章！