MySQL:总计GROUP BY WITH ROLLUP好奇心

我有两个问题.其中一个对我有意义,另一个则没有.第一:

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

结果相同.但为什么我要总共得到乌兹别克斯坦？

但为什么我要总共得到乌兹别克斯坦？

因为您没有选择您正在进行GROUPING BY的项目.如果你说:

GROUP BY c.printable_name

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

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

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扩展在它的工作方式上有点像黑客.在末尾的汇总行阶段,它在整个预分组结果集上运行以获取其值,然后将group-by列设置为NULL.它也不会使对该列具有功能依赖性的其他列无效.它可能应该,但MySQL目前并不真正了解功能依赖的整个事情.

因此,如果您选择c.printable_name,它将显示它随机选择的任何国家/地区名称值,如果您选择c.country_id,它将显示它随机选择的任何国家/地区ID - 即使c.country_id是加入条件,因此必须是与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具有功能依赖性的列.所以它会使有效查询也会失败,因此通常无用.)