Mysql Group by 与 With Rollup

最新推荐文章于 2021-02-08 02:25:10 发布

Robin2Wu

最新推荐文章于 2021-02-08 02:25:10 发布

阅读量117

点赞数

分类专栏：深入浅出 - Mysql 数据库开发、优化与管理维护文章标签： MySQL

深入浅出 - Mysql 数据库开发、优化与管理维护专栏收录该内容

6 篇文章 0 订阅

订阅专栏


create table sales (
  `year` int not null,
  `country` varchar(20) not null,
  `product` varchar(32) not null,
  `profit` int
);

insert into sales values(2004, "china", "tnt1", 2001);
insert into sales values(2004, "china", "tnt2", 2002);
insert into sales values(2004, "china", "tnt3", 2003);
insert into sales values(2005, "china", "tnt1", 2004);
insert into sales values(2005, "china", "tnt2", 2005);
insert into sales values(2005, "china", "tnt3", 2006);
insert into sales values(2005, "china", "tnt1", 2007);
insert into sales values(2005, "china", "tnt2", 2008);
insert into sales values(2005, "china", "tnt3", 2009);
insert into sales values(2006, "china", "tnt1", 2010);
insert into sales values(2006, "china", "tnt2", 2011);
insert into sales values(2006, "china", "tnt3", 2012);


select year, country, product, sum(profit) from sales group by year, country, product;
+------+---------+---------+-------------+
| year | country | product | sum(profit) |
+------+---------+---------+-------------+
| 2004 | china   | tnt1    |        2001 |
| 2004 | china   | tnt2    |        2002 |
| 2004 | china   | tnt3    |        2003 |
| 2005 | china   | tnt1    |        4011 |
| 2005 | china   | tnt2    |        4013 |
| 2005 | china   | tnt3    |        4015 |
| 2006 | china   | tnt1    |        2010 |
| 2006 | china   | tnt2    |        2011 |
| 2006 | china   | tnt3    |        2012 |
+------+---------+---------+-------------+
9 rows in set (0.00 sec)


select year, country, product, sum(profit) from sales group by year, country, product with rollup;
+------+---------+---------+-------------+
| year | country | product | sum(profit) |
+------+---------+---------+-------------+
| 2004 | china   | tnt1    |        2001 |
| 2004 | china   | tnt2    |        2002 |
| 2004 | china   | tnt3    |        2003 |
| 2004 | china   | NULL    |        6006 |
| 2004 | NULL    | NULL    |        6006 |
| 2005 | china   | tnt1    |        4011 |
| 2005 | china   | tnt2    |        4013 |
| 2005 | china   | tnt3    |        4015 |
| 2005 | china   | NULL    |       12039 |
| 2005 | NULL    | NULL    |       12039 |
| 2006 | china   | tnt1    |        2010 |
| 2006 | china   | tnt2    |        2011 |
| 2006 | china   | tnt3    |        2012 |
| 2006 | china   | NULL    |        6033 |
| 2006 | NULL    | NULL    |        6033 |
| NULL | NULL    | NULL    |       24078 |
+------+---------+---------+-------------+
16 rows in set (0.00 sec)

[b]注意: Rollup 与 order by 相互排拆[/b]


select year, country, product, sum(profit) from sales group by year, country, product with rollup order by product;
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY