SQL 计算所有行而不是计算单个行

2021-11-20 00:00:00 sql mysql

我有一个从数据库请求数据的 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)

该语句应为底行提供 1 的 Aura 计数，并为顶行提供 2 的 Aura 计数.怎么了?

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)

相关文章