MySQL 工作组 暂缺_» 关于Mysql group by和with rollup的用法小记 Sphinx 中文站

今在实现某功能时遇一sql，用到分组聚合功能。记起一书中曾提起过with rollup可组内聚合。故特弄清其中细节及注意事项。

书中原始实例：

========================================

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);

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);

Jave代码

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 (00 sec)

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 (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 (00 sec)

从上面的例子中可以看到第二个sql语句的结果比第一个sql语句的结果多出了很多行。而这些行反应出了更多信息。滴入，第二个sql语句的结果的前三行表示2004年在中国的各个产品(tnt1、tnt2、tnt3)的利润。而第四行表示2004年在中国所有产品的利润是 6006，这个信息在第一个sql语句中是不能反应出来的。第5行表示2004年全世界国有产品的利润是6006

其实with rollup反映的是一种OLAP思想，也就是说这个GROUP BY 语句执行完成后可以满足用户想要得到的任何一个分组及分组祝贺的聚合信息值。

注意：1、当使用ROLLUP时，不能同时使用ORDER BY 子句进行结果排序。即，ROLLUP和ORDER BY 是互斥的。

2、limit用在ROLLUP后面