SQL COUNT(*) が ID とポスターでグループ化せずにすべての行を集計するのはなぜですか?

*SQL COUNT() による行の誤った集計: よくある落とし穴**

SQL クエリで頻繁に発生する問題には、COUNT(*) 集計関数が意図したグループ化を実行せずに予期せずすべての行をカウントしてしまうことが含まれます。 これは多くの場合、GROUP BY 句の不適切な配置または省略が原因で発生します。

問題のあるクエリとその解決策を調べてみましょう:

元のクエリは、「ポスター」と「ID」でグループ化された「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
    ) 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 中国語 Web サイトの他の関連記事を参照してください。