MySQL 按组对“汇总”进行排序

sort the "rollup" in group by

提问by shantanuo

I found that the "with rollup" option used with group by is very useful. But it does not behave with "order by" clause. Is there any way to order by the way I want as well as calculate the sub-totals?

我发现与 group by 一起使用的“with rollup”选项非常有用。但它不符合“order by”子句。有什么方法可以按我想要的方式订购并计算小计吗？

CREATE TABLE `mygroup` (
  `id` int(11) default NULL,
  `country` varchar(100) default NULL
) ENGINE=MyISAM ;

INSERT INTO `mygroup` VALUES (1,'India'),(5,'India'),(8,'India'),(18,'China'),(28,'China'),(28,'China');

mysql>select country, sum(id) from mygroup group by country with rollup; 
+---------+---------+
| country | sum(id) |
+---------+---------+
| China   |      74 | 
| India   |      14 | 
| NULL    |      88 | 
+---------+---------+
3 rows in set (0.00 sec)

mysql>select country, sum(id) as cnt from mygroup group by country order by cnt ;
+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
+---------+------+
2 rows in set (0.00 sec)

mysql>select country, sum(id) as cnt from mygroup group by country with rollup order by cnt;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

Expected Result:
+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
| NULL    |   88 | 
+---------+---------+
3 rows in set (0.00 sec)

回答by valli

try like using temporary table

尝试使用临时表

 SELECT * 
 FROM 
 (
     SELECT country, sum(id) as cnt 
     FROM mygroup GROUP BY country WITH rollup
 ) t 
 ORDER BY cnt;

This article may help you link text

这篇文章可以帮助你链接文字

回答by sig11

Have you tried putting the order in the grouping?

您是否尝试将订单放在分组中？

SELECT country, SUM(id) 
    FROM mygroup 
GROUP BY country DESC WITH ROLLUP;

Should return:

应该返回：

+---------+---------+
| country | SUM(id) |
+---------+---------+
| India   |      14 |
| China   |      74 |
| NULL    |      88 |
+---------+---------+

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html

回答by vivek s vamja

You can try this query:

你可以试试这个查询：

 SELECT country,id
     FROM mygroup GROUP BY country ASC WITH ROLLUP

回答by ysth

The two subqueries solution is needlessly complicated. You just need one, and no serial number.

两个子查询的解决方案是不必要的复杂。你只需要一个，不需要序列号。

select country, cnt
from (
    select
        country,
        sum(id) as cnt
    from mygroup
    group by country with rollup
) mygroup_with_rollup
order by country is null, cnt, country;

The country is nullplaces the rollup row at the end.

将country is null汇总行放在最后。

回答by Rockallite

Solution

解决方案

Use two sub-queries, like this:

使用两个子查询，如下所示：

-- 3. Filter the redundant rows. Don't use HAVING; it produces a redundant row
--    when there's only one country.
SELECT r2.country, r2.cnt
FROM (
    -- 2. Save the ordering by giving each row an increasing serial number. 
    --    By grouping by serial numbers and country counts with rollup, the
    --    ordering is preserved and rollup data is calculated.
    SELECT (@s := @s + 1) AS sn, r1.country, SUM(r1.cnt) AS cnt
    FROM (
        -- 1. Calculate the country counts and order by them
        SELECT country, SUM(id) AS cnt
        FROM mygroup
        GROUP BY 1
        ORDER BY 2
    ) r1, (SELECT @s := 0) x
    GROUP BY 1, 2 WITH ROLLUP
) r2
WHERE r2.country IS NOT NULL OR r2.sn IS NULL

Result should be ordered by cntand with sub-totals in the last row:

结果应按cnt最后一行中的小计排序：

+---------+------+
| country | cnt  |
+---------+------+
| India   |   14 | 
| China   |   74 | 
| NULL    |   88 | 
+---------+------+
3 rows in set (0.00 sec)