MySQL中的WITH ROLLUP

MySQL的扩展SQL中有一个非常有意思的应用WITH ROLLUP，在分组的统计数据的基础上再进行相同的统计（SUM,AVG,COUNT…），非常类似于Oracle中统计函数的功能，Oracle的统计函数更多更强大。

下面演示单个司机以及所有司机的总行驶里程数和平均行驶里程数：

mysql> select name,sum(miles) as 'miles/driver'

    -> from driver_log group by name with rollup;

+-------+--------------+

| name  | miles/driver |

+-------+--------------+

| Ben   |          362 |

| Henry |          911 |

| Suzi  |          893 |

| NULL  |         2166 |

+-------+--------------+

4 rows in set (0.00 sec)

 

mysql> select name,avg(miles) as driver_avg

    -> from driver_log group by name with rollup;

+-------+------------+

| name  | driver_avg |

+-------+------------+

| Ben   |   120.6667 |

| Henry |   182.2000 |

| Suzi  |   446.5000 |

| NULL  |   216.6000 |

+-------+------------+

4 rows in set (0.00 sec)

 

mysql> select name,sum(miles) as 'miles/driver',avg(miles) as driver_avg

    -> from driver_log group by name with rollup;

+-------+--------------+------------+

| name  | miles/driver | driver_avg |

+-------+--------------+------------+

| Ben   |          362 |   120.6667 |

| Henry |          911 |   182.2000 |

| Suzi  |          893 |   446.5000 |

| NULL  |         2166 |   216.6000 |

+-------+--------------+------------+

4 rows in set (0.00 sec)

在多个分组下WITH ROLLUP同样有效：

mysql> select srcuser,dstuser,count(*) from mail group by srcuser,dstuser;

+---------+---------+----------+

| srcuser | dstuser | count(*) |

+---------+---------+----------+

| barb    | barb    |        1 |

| barb    | tricia  |        2 |

| gene    | barb    |        2 |

| gene    | gene    |        3 |

| gene    | tricia  |        1 |

| phil    | barb    |        1 |

| phil    | phil    |        2 |

| phil    | tricia  |        2 |

| tricia  | gene    |        1 |

| tricia  | phil    |        1 |

+---------+---------+----------+

10 rows in set (0.05 sec)

 

mysql> select srcuser,dstuser,count(*) from mail group by srcuser,dstuser with rollup;

+---------+---------+----------+

| srcuser | dstuser | count(*) |

+---------+---------+----------+

| barb    | barb    |        1 |

| barb    | tricia  |        2 |

| barb    | NULL    |        3 |

| gene    | barb    |        2 |

| gene    | gene    |        3 |

| gene    | tricia  |        1 |

| gene    | NULL    |        6 |

| phil    | barb    |        1 |

| phil    | phil    |        2 |

| phil    | tricia  |        2 |

| phil    | NULL    |        5 |

| tricia  | gene    |        1 |

| tricia  | phil    |        1 |

| tricia  | NULL    |        2 |

| NULL    | NULL    |       16 |

+---------+---------+----------+

15 rows in set (0.00 sec)