MySQL中的WITH ROLLUP

標籤：blog   http   資料   art   div   ar   sql   .net   

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)