查询：每个项目计数多个聚合

Question

通常，您需要显示数据库项目的列表以及有关每个项目的特定汇总号。例如，当您在“堆栈溢出”中键入标题文本时，将出现“相关问题”列表。该列表显示了相关条目的标题以及每个标题的响应数量的单个汇总数量。

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

• 我的商品的名称（共15个，我拥有的13个）
• 我的商品的名称（共15个）
• 我的商品的名称（我拥有13个）

我的数据库是：

• items ：itemId，itemName，ownerId
• categories ：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

但是，如何在单个查询中同时获取它们？即：类别名称，仅此owner_id的每个类别的项目ID的数量，每个类别的项目ID的数量

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

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

Answer 1

这是一个技巧：计算SUM()已知为1或0COUNT()的值的a等于该值为1的行中的a。并且您知道布尔比较返回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条款。在选择列表中的所有表达式都被求值之后，将运行这些子句。