SQL对所有行进行计数，而不是对单个行进行计数

问题描述

我有一条SQL语句，该语句从数据库请求数据.

I have a SQL statement which requests data from the database.

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

这是我的messages_aura表布局.它显示ID，Status和UserID.

Here is my messages_aura table layout. It shows ID, Status and UserID.

这是上面语句的输出.

(上面屏幕截图中的ID指的是下面的Poster，上面屏幕截图中的Status指的是下面的ID)

(The ID from the above screenshot refers to Poster below and the Status from the above screenshot refers to ID below)

该语句应使最下面一行的Aura计数为1，最上面一行的Aura计数为2.怎么了?

The statement should give the bottom row a Aura count of 1 and the top row an Aura count of 2. What's wrong?

推荐答案

您遗漏了GROUP BY，因此它计算的是所有内容，而不是按某些列进行分组.

You're missing GROUP BY, so it's counting everything instead of grouping by some columns.

LEFT JOIN
(
    select `ID` as `AuraID`, `Status` as `AuraStatus`, count(*) as `Aura`
    from messages_aura
    GROUP BY AuraID, AuraStatus
) aura ON (var.Poster = aura.AuraID AND var.ID = aura.AuraStatus)

这篇关于SQL对所有行进行计数，而不是对单个行进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！