转自：[https://www.yiibai.com/sql/sql-grouping-sets.html](https://www.yiibai.com/sql/sql-grouping-sets.html)

在本教程中，您将学习如何使用SQL `GROUPING SETS`运算符生成多个分组集。

# 创建样本表

让我们创建一个名为`inventory`的新表来演示`GROUPING SETS`的功能。

首先，创建一个名为`inventory`的新表：

```sql
CREATE TABLE inventory (
    warehouse VARCHAR(255),
    product VARCHAR(255) NOT NULL,
    model VARCHAR(50) NOT NULL,
    quantity INT,
    PRIMARY KEY (warehouse,product,model)
);
```

第二步，将数据插入`inventory`表：

```sql
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose', 'iPhone','6s',100);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','6s',50);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','iPhone','7',50);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','7',10);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','iPhone','X',150);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco', 'iPhone','X',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','Samsung','Galaxy S',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco','Samsung','Galaxy S',200);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Fransisco','Samsung','Note 8',100);
INSERT INTO inventory(warehouse, product, model, quantity)
VALUES('San Jose','Samsung','Note 8',150);
```

第三，查询`inventory`表中的数据：

```
SELECT 
    *
FROM
    inventory;
+---------------+---------+----------+----------+
| warehouse     | product | model    | quantity |
+---------------+---------+----------+----------+
| San Jose      | iPhone  | 6s       |      100 |
| San Fransisco | iPhone  | 6s       |       50 |
| San Jose      | iPhone  | 7        |       50 |
| San Fransisco | iPhone  | 7        |       10 |
| San Jose      | iPhone  | X        |      150 |
| San Fransisco | iPhone  | X        |      200 |
| San Jose      | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Galaxy S |      200 |
| San Fransisco | Samsung | Note 8   |      100 |
| San Jose      | Samsung | Note 8   |      150 |
+---------------+---------+----------+----------+
10 rows in set
```

# GROUPING SETS简介

分组集是一组使用`GROUP BY`子句进行分组的列。 通常，单个聚合查询定义单个分组集。

以下示例定义分组集(仓库，产品)。 它返回仓库和产品中存储在库存中的库存单位数(SKU)。

```sql
SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product;
```

执行上面查询语句，得到以下结果：

```
   warehouse   | product | qty 
---------------+---------+-----
 San Fransisco | iPhone  | 260
 San Fransisco | Samsung | 300
 San Jose      | iPhone  | 300
 San Jose      | Samsung | 350
```

以下查询查找仓库的SKU数量。 它定义了分组集(`warehouse`)：

```sql
SELECT
    warehouse, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse;
+---------------+-----+
| warehouse     | qty |
+---------------+-----+
| San Fransisco | 560 |
| San Jose      | 650 |
+---------------+-----+
2 rows in set
```

以下查询返回产品的SKU数。 它定义了分组集(`product`)：

```sql
SELECT
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product;
```

执行上面查询语句，得到以下结果：

```shell
+---------+-----+
| product | qty |
+---------+-----+
| iPhone  | 560 |
| Samsung | 650 |
+---------+-----+
2 rows in set
```

以下查询查找所有仓库和产品的SKU数。 它定义了一个空的分组集`()`。

```sql
SELECT
    SUM(quantity) qty
FROM
    inventory;
```

执行上面示例代码，得到以下结果：

```
+------+
| qty  |
+------+
| 1210 |
+------+
1 row in set
```

到目前为止，我们有四个分组集:(`warehouse`, `product`)，(`warehouse`)，(`product`)和`()`。 要使用单个查询返回所有分组集，可以使用UNION ALL运算符组合上面的所有查询。

`UNION ALL`要求所有结果集具有相同的列数，因此，需要将`NULL`添加到每个查询的选择列表中，如下所示：

```sql
SELECT
    warehouse,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product
UNION ALL
SELECT
    warehouse, 
    null,
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    warehouse
UNION ALL
SELECT
    null,
    product, 
    SUM(quantity) qty
FROM
    inventory
GROUP BY
    product
UNION ALL
SELECT
    null,
    null,
    SUM(quantity) qty
FROM
    inventory;
SQL
```

执行上面查询语句，得到以下结果：

```
+---------------+---------+------+
| warehouse     | product | qty  |
+---------------+---------+------+
| San Fransisco | iPhone  | 260  |
| San Fransisco | Samsung | 300  |
| San Jose      | iPhone  | 300  |
| San Jose      | Samsung | 350  |
| San Fransisco | NULL    | 560  |
| San Jose      | NULL    | 650  |
| NULL          | iPhone  | 560  |
| NULL          | Samsung | 650  |
| NULL          | NULL    | 1210 |
+---------------+---------+------+
9 rows in set
```

从输出中可以清楚地看到，查询生成了一个结果集，其中包含所有分组集的聚合。尽管查询按预期工作，但它有两个主要问题：

* 首先，查询语句很难阅读，因为它很冗长。
* 其次，它存在性能问题，因为数据库系统必须多次扫描库存表。

为解决这些问题，SQL提供了`GROUPING SETS`。`GROUPING SETS`是`GROUP BY`子句的一个选项。`GROUPING SETS`在同一查询中定义多个分组集。

以下是`GROUPING SETS`选项的一般语法：

```sql
SELECT
    c1,
    c2,
    aggregate (c3)
FROM
    table
GROUP BY
    GROUPING SETS (
        (c1, c2),
        (c1),
        (c2),
        ()
);
```

此查询定义了四个分组集`(c1，c2)`，`(c1)`，`(c2)`和`()`。可以使用`GROUPING SETS`将上面的`UNION ALL`子句查询重写：

```sql
SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );
```

执行上面查询语句，得到以下结果：

```
+---------------+---------+------+
| warehouse     | product | qty  |
+---------------+---------+------+
| San Fransisco | iPhone  | 260  |
| San Fransisco | Samsung | 300  |
| San Jose      | iPhone  | 300  |
| San Jose      | Samsung | 350  |
| San Fransisco | NULL    | 560  |
| San Jose      | NULL    | 650  |
| NULL          | iPhone  | 560  |
| NULL          | Samsung | 650  |
| NULL          | NULL    | 1210 |
+---------------+---------+------+
9 rows in set
```

此查询比上面的查询更具可读性和执行速度，因为数据库系统不必多次读取库存表。