Mysql的with rollup分组统计功能（5.1以上版本）
RollUp是上卷功能，类似于数据挖掘中的上卷操作。
ROLLUp的功能和Order by功能是互斥的。
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
+------+-------------+
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | Finland | Phone | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
+------+---------+------------+-------------+
mysql> SELECT year, country, product, SUM(profit)
-> FROM sales
-> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2000 | Finland | NULL | 1600 |
| 2000 | India | Calculator | 150 |
| 2000 | India | Computer | 1200 |
| 2000 | India | NULL | 1350 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2000 | USA | NULL | 1575 |
| 2000 | NULL | NULL | 4525 |
| 2001 | Finland | Phone | 10 |
| 2001 | Finland | NULL | 10 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 2700 |
| 2001 | USA | TV | 250 |
| 2001 | USA | NULL | 3000 |
| 2001 | NULL | NULL | 3010 |
| NULL | NULL | NULL | 7535 |
+------+---------+------------+-------------+
最新文章
- js 数组删去重复的加上没有的元素
- 与你相遇好幸运，Mongodb客户端&BUGS
- os.path 大全
- C++中的虚函数(表)实现机制以及用C语言对其进行的模拟实现
- Spring MVC Test -Controller
- nodejs入门（一）
- 支付宝C# RSA签名 报系统找不到指定的文件问题解决
- 【函数】oracle translate() 详解+实例
- HBase(二): c#访问HBase之股票行情Demo
- vim的.vimrc文件设置
- C#发送邮件源码
- 域名转化到IP地址的实现
- （ASP页面查询等待提示效果）GridViewなどで検索中に「処理中メッセージ」を表示する方法（※他の長い時間処理も参照できる）
- 网络基础---OSI 模型与TCP/IP
- java 构造函数是如何执行的
- javacsript （十一） 对象
- redis 基础学习总结
- Kvm虚拟化的一种打包及部署方案（采用tar包，lvm方式）
- 小白学爬虫-在无GUI的CentOS上使用Selenium+Chrome
- Docker下安装GitLab