我有一个从数据库请求数据的 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)
) final
GROUP BY `ID`, `Poster`
ORDER BY `Time` DESC LIMIT 10
这是我的messages_aura
表格布局。表明ID
, Status
and UserID
.
这是上述语句的输出。
(The ID
从上面的截图中可以看出Poster
下面和Status
从上面的截图中可以看出ID
below)
该语句应为底行提供 Aura 计数1
顶行的光环计数为2
。怎么了?
你失踪了GROUP BY
,所以它对所有内容进行计数,而不是按某些列进行分组。
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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)