在 GROUP BY 和 COUNT 之后加入另一个表

Question

我试图了解使用 JOIN、COUNT(*) 和 GROUP BY 进行非常简单的查询的正确方法。我实际上已经让它工作了（见下文），但从我读到的内容来看，我使用了一个额外的 GROUP BY ，这是我不应该使用的。

（注意：下面的问题不是我的实际问题（它处理更复杂的表），但我试图提出一个类似的问题）

我有两个表：

Table: Person
-------------
key  name     cityKey
1    Alice    1
2    Bob      2
3    Charles  2
4    David    1

Table: City
-------------
key  name
1    Albany
2    Berkeley
3    Chico

我想对 People 进行查询（带有一些 WHERE 子句）返回

• 每个城市中匹配的人数
• 城市的键 城市
• 的名称。

如果我这样做，

SELECT COUNT(Person.key) AS count, City.key AS cityKey, City.name AS cityName
FROM Person 
LEFT JOIN City ON Person.cityKey = City.key 
GROUP BY Person.cityKey, City.name

我会得到我想要的结果

count   cityKey   cityName
2       1         Albany
2       2         Berkeley

但是，我read 放入 GROUP BY 子句 (City.name) 的最后一部分只是为了使其工作错误的。

那么这样做的正确方法是什么？我一直在尝试用谷歌搜索答案，但我觉得有一些基本的东西我没有得到。

Answer 1

我不认为在这种情况下这是“错误的”，因为城市名称和城市键之间存在一对一的关系。您可以重写它，以便加入子选择以按键获取城市的人数，然后再次获取城市表的名称，但这是有争议的，这是否会更好。我想这是风格和观点的问题。

select PC.ct, City.key, City.name
  from City
  join (select count(Person.key) ct, cityKey key from Person group by cityKey) PC
    on City.key = PC.key

如果我的 SQL 不是太生疏的话:-)

Answer 2

...我读到，仅仅为了使其工作而添加 GROUP BY 子句的最后一部分 (City.name) 是错误的。

你误会了，你搞反了。
标准 SQL 要求您在 GROUP BY 中指定 SELECT 中提到的所有未包装在聚合函数中的列。如果您不希望 GROUP BY 中的某些列，请将它们包装在聚合函数中。根据数据库的不同，您可以使用分析/窗口函数OVER...

但是，MySQL 和 SQLite 提供了“功能”，您可以在其中从分组依据中省略这些列 - 这会导致没有结果“为什么这个不从 MySQL 移植到 fill_in_the_blank 数据库？！” Stackoverflow 和许多其他网站 &论坛。

Answer 3

但是，我读过
GROUP BY 子句的最后一部分
(City.name) 只是为了让它发挥作用
错了。

这并没有错。您必须了解查询优化器如何看待您的查询。解析它的顺序就是要求您“将最后一部分放入”。优化器以类似于以下顺序的方式查看您的查询：

• 连接所需的表；
• 通过 WHERE 子句过滤复合数据
• 集；通过 GROUP BY 子句将剩余的行分成组，并聚合
• 它们，然后通过 HAVING 再次过滤它们。子句
• 最终通过 SELECT / ORDER BY、UPDATE 或 DELETE 进行操作。

这里的要点是，GROUP BY 并不必须命名​​ SELECT 中的所有列，但事实上恰恰相反 - SELECT 不能包含 GROUP BY 中尚未包含的任何列。

Answer 4

您的查询仅适用于 MySQL，因为您对 Person.cityKey 进行分组，但选择了 city.key。所有其他数据库都要求您使用 min(city.key) 之类的聚合，或者将 City.key 添加到 group by 子句中。

由于城市名称和城市键的组合是唯一的，因此以下内容是等效的：

select    count(person.key), min(city.key), min(city.name)
...
group by  person.citykey

或：

select    count(person.key), city.key, city.name
...
group by  person.citykey, city.key, city.name

或：

select    count(person.key), city.key, max(city.name)
...
group by  city.key

组中的所有行都将具有相同的城市名称和键，因此使用 max< 并不重要/code> 或 min 聚合。

PS 如果您只想计算不同的人，即使他们有多行，请尝试：

count(DISTINCT person.key)

而不是

count(person.key)