MySQL:总的 GROUP BY WITH ROLLUP 好奇心

Question

我有两个问题。其中一个对我有意义，另一个对我没有意义。第一个:

SELECT gender AS 'Gender', count(*) AS '#'
    FROM registrations 
    GROUP BY gender WITH ROLLUP

这给了我这个:

Gender       #
Female      20
Male        19
NULL        39

所以，我得到了计数和总计数。我所期望的。下一个:

SELECT c.printable_name AS 'Country', count(*) AS '#' 
    FROM registrations r 
    INNER JOIN country c ON r.country = c.country_id 
    GROUP BY country WITH ROLLUP

Country         #
Denmark         9
Norway         10
Sweden         18
United States   1
Uzbekistan      1
Uzbekistan     39

同样的结果。但为什么我得到乌兹别克斯坦的总数？？

Answer 1

But why do I get Uzbekistan for the total??

因为您没有选择要分组的项目。如果你说:

GROUP BY c.printable_name

您会得到预期的 NULL。但是，您按不同的列进行分组，因此 MySQL 不知道 printable_name 正在参与汇总组，并在 所有 注册的连接中从该列中选择任何旧值。 (所以您可能会看到乌兹别克斯坦以外的其他国家/地区。)

这是一个更广泛的问题的一部分，MySQL 允许您在 GROUP BY 查询中选择什么。例如，你可以说:

SELECT gender FROM registrations GROUP BY country;

即使国家和性别之间没有直接的因果关系(又名“功能依赖”)，MySQL 也会很高兴地为每个国家的注册选择一个性别值。其他 DBMS 将拒绝上述命令，理由是不能保证每个国家都是一种性别。(*)

现在，这个:

SELECT c.printable_name AS 'Country', count(*) AS '#' 
FROM registrations r 
INNER JOIN country c ON r.country = c.country_id 
GROUP BY country

没问题，因为 r.country 和 c.printable_name 之间存在函数依赖关系(假设您已将 country_id 正确描述为 PRIMARY KEY)。

然而，MySQL 的 WITH ROLLUP 扩展在其工作方式上有点乱七八糟。在最后的汇总行阶段，它遍历整个预分组结果集以获取其值，然后然后将分组依据列设置为 NULL。 它不会同时清空对该列具有功能依赖性的其他列。它可能应该，但 MySQL 目前并不真正了解有关功能依赖性的全部内容。

因此，如果您选择 c.printable_name，它会显示它随机选择的国家名称值，如果您选择 c.country_id，它会显示它随机选择的国家 ID⟩— 即使 c.country_id 是连接标准， so 必须与 r.country 相同，为 NULL!

解决该问题的方法是:

• 改为按 printable_name 分组；如果 printable_names 是唯一的，应该没问题，或者
• 选择“r.country”以及 printable_name，并检查是否为 NULL，或者
• 忘记 WITH ROLLUP 并单独查询最终总和。这会稍慢一些，但它也符合 ANSI SQL-92，因此您的应用程序可以在其他数据库上运行。

(*: MySQL 有一个 SQL_MODE 选项 ONLY_FULL_GROUP_BY 应该可以解决这个问题，但它走得太远了，只允许您从 GROUP BY 中选择列，而不是对 GROUP BY 具有功能依赖性的列. 所以它也会使有效查询失败，使其通常无用。)