問題描述

我有一個從數據庫請求數據的 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)

這篇關于SQL 計算所有行而不是計算單個行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！