mysql - 查询 : count multiple aggregates per item

Question

通常您需要显示数据库项目的列表和每个项目的特定总数。例如，当您在 Stack Overflow 上键入标题文本时，会出现相关问题列表。该列表显示了相关条目的标题以及每个标题的单个聚合响应数量。

我有一个类似的问题，但需要多个聚合。我想根据用户选项以 3 种格式中的任何一种显示项目列表:

• 我的元素名称(总共 15 个，我拥有 13 个)
• 我的项目名称(共 15 个)
• 我的元素名称(我拥有 13 个)

我的数据库是:

• 项目:itemId、itemName、ownerId
• 类别:catId、catName
• map :mapId、itemId、catId

下面的查询得到:类别名称，每个类别的项目 id 数

SELECT
  categories.catName,
  COUNT(map.itemId) AS item_count
FROM categories
LEFT JOIN map
  ON categories.catId = map.catId
GROUP BY categories.catName

这个获取:类别名称，仅此 owner_id 的每个类别的项目 ID 计数

SELECT categories.catName, 
COUNT(map.itemId) AS owner_item_count
FROM categories
LEFT JOIN map
  ON categories.catId = map.catId
LEFT JOIN items
  ON items.itemId = map.itemId
WHERE owner = @ownerId
GROUP BY categories.catId

但是我如何在一个查询中同时获取它们呢？即:类别名称、每个类别的项目 ID 计数、仅此 owner_id 的每个类别的项目 ID 计数

奖金。我怎样才能有选择地只检索 where catId count != 0 对于其中的任何一个？在尝试“WHERE item_count <> 0”时，我得到:

MySQL said: Documentation
#1054 - Unknown column 'rid_count' in 'where clause' 

Answer 1

这里有一个技巧:计算已知为 1 或 0 的值的 SUM() 等同于值所在行的 COUNT()是 1。您知道 bool 比较返回 1 或 0(或 NULL)。

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  LEFT JOIN map m USING (catid)
  LEFT JOIN items i USING (itemid)
GROUP BY c.catid;

至于奖励问题，您可以简单地进行内部联接而不是外部联接，这意味着只会返回在 map 中至少有一行的类别。

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  INNER JOIN map m USING (catid)
  INNER JOIN items i USING (itemid)
GROUP BY c.catid;

这是另一个解决方案，效率不高，但我会展示它来解释为什么会出现错误:

SELECT c.catname, COUNT(m.catid) AS item_count,
  SUM(i.ownerid = @ownerid) AS owner_item_count
FROM categories c
  LEFT JOIN map m USING (catid)
  LEFT JOIN items i USING (itemid)
GROUP BY c.catid
HAVING item_count > 0;

您不能在 WHERE 子句中使用列别名，因为 WHERE 子句中的表达式先于选择列表中的表达式求值。换句话说，与选择列表表达式关联的值尚不可用。

您可以在 GROUP BY、HAVING 和 ORDER BY 子句中使用列别名。这些子句在选择列表中的所有表达式都已求值后运行。