对group by中的“汇总”进行排序
对group by中的“汇总”进行排序
提问于 2009-11-20 13:39:39
我发现与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)回答 6
Stack Overflow用户
回答已采纳
发布于 2009-11-20 13:49:10
尝试使用临时表
SELECT *
FROM
(
SELECT country, sum(id) as cnt
FROM mygroup GROUP BY country WITH rollup
) t
ORDER BY cnt;这篇文章可能对你link text有帮助。
Stack Overflow用户
发布于 2012-09-26 01:20:02
您试过将订单放入分组中吗？
SELECT country, SUM(id)
FROM mygroup
GROUP BY country DESC WITH ROLLUP;应返回：
+---------+---------+
| country | SUM(id) |
+---------+---------+
| India | 14 |
| China | 74 |
| NULL | 88 |
+---------+---------+http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
Stack Overflow用户
发布于 2015-11-02 15:43:44
您可以尝试执行以下查询：
SELECT country,id
FROM mygroup GROUP BY country ASC WITH ROLLUP页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1768547
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号