MYSQL GROUP BY WITH ROLLUP 使具有最多 NULL 值的元组首先出现

Question

我需要下面代码的输出

SELECT Model, Make, Country, owner, condition, COUNT(*)
FROM test
GROUP BY Model, Make, Country, owner, condition WITH ROLLUP;

按每个元组中 NULL 值的数量排序。这意味着首先出现一个有 5 个 NULL 的双元组，然后是一个有 4 个 NULL 的，依此类推。

我尝试按 ASC 进行 GROUP BY，但它不起作用。有什么想法可以轻松完成吗？

Answer 1

基本上，GROUP BY WITH ROLLUP 与排序不兼容。但是你可以将它放入子查询中并像这样排序:

SELECT *
FROM
  (
    SELECT Model, Make, Country, owner, condition, COUNT(*) AS items_count
    FROM test
    GROUP BY Model, Make, Country, owner, condition WITH ROLLUP
  ) AS t
ORDER BY Model, Make, Country, owner, condition

MySQL 排序算法会将空值放在第一位，因为 NULL 总是小于某个值。