假设你有一个数据库，保存销售信息，如订单、项目等。 这两个表之间存在一对多的关系。 你想在查询中用ROLLUP计算小计，为每个订单创建发票。这个例子向我们展示了如何能够做到这一点。
数据库
桌子
mysql> DESCRIBE orders;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| created_at | datetime | NO | | NULL | |
+------------+------------------+------+-----+---------+----------------+
mysql> DESCRIBE items;
+-----------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | | |
| qty | int(10) unsigned | NO | | NULL | |
| price | decimal(11,2) unsigned | NO | | NULL | |
| orders_id | int(10) unsigned | NO | MUL | NULL | |
+-----------+------------------------+------+-----+---------+----------------+
数据
mysql> SELECT * FROM orders;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2010-01-01 00:00:00 |
| 2 | 2010-02-01 00:00:00 |
+----+---------------------+
mysql> SELECT * FROM items;
+----+----------+-----+----------+-----------+
| id | name | qty | price | orders_id |
+----+----------+-----+----------+-----------+
| 1 | bmw | 1 | 20000.00 | 1 |
| 2 | mercedes | 2 | 25000.00 | 1 |
| 3 | audi | 1 | 19000.00 | 1 |
| 4 | toyota | 3 | 15000.00 | 2 |
| 5 | mazda | 2 | 13000.00 | 2 |
+----+----------+-----+----------+-----------+
查询
mysql> SELECT
-> orders.id AS OrderID,
-> items.name AS ItemName,
-> SUM(items.price) AS ItemPrice,
-> SUM(items.qty) AS ItemQty,
-> SUM(items.qty * items.price) AS OrderTotal
-> FROM orders
-> INNER JOIN items ON orders.id = items.orders_id
-> GROUP BY OrderID, ItemName WITH ROLLUP;
结果
+---------+----------+-----------+---------+------------+
| OrderID | ItemName | ItemPrice | ItemQty | OrderTotal |
+---------+----------+-----------+---------+------------+
| 1 | audi | 19000.00 | 1 | 19000.00 |
| 1 | bmw | 20000.00 | 1 | 20000.00 |
| 1 | mercedes | 25000.00 | 2 | 50000.00 |
| 1 | NULL | 64000.00 | 4 | 89000.00 |
| 2 | mazda | 13000.00 | 2 | 26000.00 |
| 2 | toyota | 15000.00 | 3 | 45000.00 |
| 2 | NULL | 28000.00 | 5 | 71000.00 |
| NULL | NULL | 92000.00 | 9 | 160000.00 |
+---------+----------+-----------+---------+------------+
