mysql - 汇总后排序保留总计和小计行位置

Question

这是带有汇总的 mysql 的示例结果

|country | region | value  ||--------|--------|--------||   us   |  west  |   5    ||   us   |  east  |   15   ||   us   |  north |   10   ||   us   |  total |   30   ||   uk   |  west  |   3    ||   uk   |  east  |   2    ||   uk   |  north |   1    ||   uk   |  total |   6    ||  total |  total |   36   |

我想像这样对值列进行排序，保留总计行的位置

升华

|   us   |  west  |   5    ||   us   |  north |   10   ||   us   |  east  |   15   ||   us   |  total |   30   ||   uk   |  north |   1    ||   uk   |  east  |   2    ||   uk   |  west  |   3    ||   uk   |  total |   6    ||  total |  total |   36   |

降序

|   us   |  east  |   15   ||   us   |  north |   10   ||   us   |  west  |   5    ||   us   |  total |   30   ||   uk   |  west  |   3    ||   uk   |  east  |   2    ||   uk   |  north |   1    ||   uk   |  total |   6    ||  total |  total |   36   |

这是一个查询示例:

SELECT *FROM (  SELECT   COALESCE(country, 'total') AS country,   COALESCE(region, 'total' ) AS region,   SUM('value'),   FROM table   GROUP BY country ASC, region ASC   WITH ROLLUP) tORDER BY ... maybe something to do here ...

也许是基于正则表达式的排序顺序？还是使用 mysql 字符串函数？

我不知道如何解决我的问题

提前感谢您的帮助

...

在你的帮助下和一些个人反射(reflection)之后我想我现在有一个很好的方法来做到这一点

SELECT *FROM (   SELECT    IF(IFNULL(country, 1),'total',NULL) AS country,   IF(IFNULL(region, 1),'total',NULL) AS region,   COALESCE(country, 'Total') AS country1,    COALESCE(region, 'Total') AS region1,    SUM(value) as `value to sort`   FROM data_table   GROUP BY country, region WITH ROLLUP) t ORDER BY  country IS NULL DESC, country1 ASC, region IS NULL DESC, ... ... `value to sort` DESC(or) ... `value to sort` ASC

我现在总计和小计总是在聚合和子聚合值之后，即使使用 DESC 排序 :-)

你觉得这样好吗？它在所有情况下都对我有用

Answer 1

基于原始版本Johan's answer :

SELECT *
FROM (
  SELECT 
    COALESCE(country, 'total') AS country,
    COALESCE(region, 'total' ) AS region,
    SUM(`value`) as `value`, 
  FROM `table` 
  GROUP BY country, region WITH ROLLUP
) t
ORDER BY country = 'total', country, region = 'total', `value`

这个技巧的工作方式是表达式 country = 'total'如果 country 计算为 1(真)列等于 'total' ，否则为 0(假)。在数字升序中，1 在 0 之后。因此，按该表达式排序会强制所有包含 country 的行。列等于 'total'在任何其他列之后排序。

类似地，按表达式排序 region = 'total'之前value强制所有值为 'total' 的行在他们的 region在具有相同 country 的任何其他行之后排序，不管他们的value专栏。

同样的技巧适用于其他 comparison operators也。例如，如果您想强制负值排在正值之后，您可以按 `value` < 0, `value` 对行进行排序。 .