SQL计算所有行而不是计算单个行
我有一个SQL语句,它从数据库中请求数据。
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)) finalGROUP BY `ID`, `Poster`ORDER BY `Time` DESC LIMIT 10
这是我的messages_aura
表格布局。它显示ID
,Status
和UserID
。
以下是上述声明的输出。
(ID
从上面的截图参考Poster
下面的以及Status
上面的截图参考ID
下面)
该陈述应该给出底行的Aura计数1
和顶行的Aura计数2
。怎么了?
相关分类