mysql - 试图计算从 MySQL 连接表返回的行数

Question

我正在尝试计算从 MySQL 连接表返回的行数，即:

select *, count(pets.id) as pets_per_owner 
from owners 
left join pets 
   on owners.id=pets.owner_id

问题是所有连接的行都被分组了。

有什么方法可以获取这些小计，但无需单独查询即可显示所有返回(未分组)的行？

TIA

Answer 1

问题是您使用的聚合函数没有GROUP BY，因此结果被缩减为一行。

MySQL 使用 GROUP BY 的扩展，允许选择列表中的列出现在聚合函数或 GROUP BY 子句之外，但这可能会导致意外结果。 (参见 MySQL Extensions to GROUP BY)

来自 MySQL 文档:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

为了解决这个问题，您应该做两件事，首先将 select * 替换为您需要返回的列的实际名称。其次，在您包含在选择列表中但未聚合的列上使用 GROUP BY。例如:

select owners.name, count(pets.id) as pets_per_owner 
from owners 
left join pets 
   on owners.id=pets.owner_id
group by owners.name

由于您在评论中发布了代码，因此很难准确确定您要查找的内容，但如果您想要宠物名称以及总数，您可以使用以下方法之一:

select o.name,
  p.name pet_name,
  p2.pets_per_owner
from owners o
left join pets p
  on o.id = p.owner_id
left join 
(
  select owner_id, count(*) pets_per_owner
  from pets
  group by owner_id
) p2
  on o.id = p2.owner_id
  and p.owner_id = p2.owner_id;

参见 SQL Fiddle with Demo

或者您可以使用 GROUP_CONCAT 函数在一行中列出宠物名称:

select owners.name, 
  group_concat(pets.name separator ', ') pet_name,
  count(pets.id) as pets_per_owner 
from owners 
left join pets 
   on owners.id=pets.owner_id
group by owners.name;

参见 SQL Fiddle with Demo