MySQL 左外连接和计数 - 'Where'？

Question

我有一个相对简单的查询，它返回用户个人资料以及与该用户相关的 2 个计数(流事件和库存)；

SELECT u.*, r.regionName, COUNT(i.fmid) AS invcount, COUNT(s.fmid) AS streamcount
FROM fm_users u
JOIN fm_regions r
ON u.region=r.regionid
LEFT OUTER JOIN fm_inventory i
ON u.fmid=i.fmid
LEFT OUTER JOIN fm_stream s
ON u.fmid=s.fmid
WHERE u.username='sampleuser'

库存和流值都可以为零，因此使用左外连接。

但是，这两个值当前返回所有用户的数字，而不是特定用户的数字(在每个表中始终标识为整数“fmid”)。这显然是因为查询没有为任一计数指定“哪里” - 但我不确定在哪里。如果我把最后一部分改成这样；

WHERE u.username='sampleuser' AND s.fmid=u.fmid AND i.fmid=u.fmid
GROUP BY u.fmid

它仍然返回不正确的数字，尽管“不同”的数字取决于搜索条件 - 以及 invcount 和streamcount 的名称数字。

Answer 1

您的查询交叉连接每个用户的 fm_inventory 和 fm_stream 表。例如，如果特定用户有 2 个匹配的 fm_inventory 行，并且 fm_stream 也有两个，则结果将有 4 (=2 x 2) 行，并且每行都将在两个 COUNT() 函数中计数。

完成您正在做的事情的方法是使用相关子选择来进行计数。

SELECT u.*, r.regionName, 
   (select COUNT(*) from fm_inventory i
      where i.fmid = u.fmid) AS invcount, 
   (select COUNT(*) from fm_stream s
      where s.fmid = u.fmid) AS streamcount
FROM fm_users u
JOIN fm_regions r
ON u.region=r.regionid
WHERE u.username='sampleuser'

这允许计数彼此独立。即使选择了多个用户，此操作也有效。在这种情况下，您需要一个 group by 子句，但除此之外它是相同的语法。