我发现与 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)
尝试使用临时表
SELECT *
FROM
(
SELECT country, sum(id) as cnt
FROM mygroup GROUP BY country WITH rollup
) t
ORDER BY cnt;
这篇文章可能对你有帮助链接文本
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)