查询:计算每个项目的多个聚合

通常,您需要显示数据库项目列表以及每个项目的某些聚合数字.例如,在Stack Overflow上键入标题文本时,将显示"相关问题"列表.该列表显示相关条目的标题以及每个标题的单个汇总的响应数量.

我有类似的问题,但需要多个聚合.我想根据用户选项显示3种格式的项目列表:

我的项目名称(总共15个,我拥有13个)

我的项目名称(共15个)

我的项目名称(由我拥有13个)

我的数据库是:

items: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的数量

奖金.我怎样才能选择只检索catId计数!= 0中的任何一个？在尝试"WHERE item_count <> 0"时,我得到:

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

Bill Karwin.. 5

这里有一个技巧:计算SUM()已知为1或0的值等于COUNT()值为1的行中的一个.并且您知道布尔比较返回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子句中的表达式是在select-list中的表达式之前计算的.换句话说,与select-list表达式关联的值尚不可用.

您可以在使用列别名GROUP BY,HAVING和ORDER BY条款.在评估了select-list中的所有表达式之后运行这些子句.

这里有一个技巧:计算SUM()已知为1或0的值等于COUNT()值为1的行中的一个.并且您知道布尔比较返回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子句中的表达式是在select-list中的表达式之前计算的.换句话说,与select-list表达式关联的值尚不可用.

您可以在使用列别名GROUP BY,HAVING和ORDER BY条款.在评估了select-list中的所有表达式之后运行这些子句.