mysql group by year_Mysql Group by 与 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);

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)

注意: Rollup 与 order by 相互排拆

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

分享到：

2009-01-15 12:33

浏览 2879

评论