分组查询mysql的作用_MySQL8.0:分组功能

[导读]从MySQL 8.0.1开始,服务器支持SQL GROUPING功能。 GROUPING函数用于区分表示常规行中的NULL的超级聚合行(由ROLLUP操作生成)中的所有值的集合的NULL。

从MySQL 8.0.1开始,服务器支持SQL GROUPING功能。 GROUPING函数用于区分表示常规行中的NULL的超级聚合行(由ROLLUP操作生成)中的所有值的集合的NULL。

介绍

MySQL服务器现在已经支持GROUP BY扩展ROLLUP。 以下是使用ROLLUP与GROUP BY的示例。mysql> create table t1 (a integer, b integer, c integer);

Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (111,11,11);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (222,22,22);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (111,12,12);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (222,23,23);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;

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

| a    | b    | c    |

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

|  111 |   11 |   11 |

|  222 |   22 |   22 |

|  111 |   12 |   12 |

|  222 |   23 |   23 |

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

4 rows in set (0.00 sec)

mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;

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

| a    | b    | SUM  |

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

|  111 |   11 |   11 |

|  111 |   12 |   12 |

|  111 | NULL |   23 |

|  222 |   22 |   22 |

|  222 |   23 |   23 |

|  222 | NULL |   45 |

| NULL | NULL |   68 |

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

7 rows in set (0.00 sec)

正如我们在上面的结果中看到的，每个超级聚合行的ROLLUP修饰符都添加了NULL。

现在我们将NULL添加到表数据中：mysql> INSERT INTO t1 values (1111,NULL,112);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 values (NULL,112,NULL);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

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

| a    | b    | c    |

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

|  111 |   11 |   11 |

|  222 |   22 |   22 |

|  111 |   12 |   12 |

|  222 |   23 |   23 |

| 1111 | NULL |  112 |

| NULL |  112 | NULL |

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

6 rows in set (0.00 sec)

当在表数据中添加NULL之后，当我们用ROLLUP查询数据时，我们有以下结果。mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;

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

| a    | b    | SUM  |

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

| NULL |  112 | NULL |

| NULL | NULL | NULL |

|  111 |   11 |   11 |

|  111 |   12 |   12 |

|  111 | NULL |   23 |

|  222 |   22 |   22 |

|  222 |   23 |   23 |

|  222 | NULL |   45 |

| 1111 | NULL |  112 |

| 1111 | NULL |  112 |

| NULL | NULL |  180 |

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

11 rows in set (0.01 sec)

正如我们在上面的例子中看到的，现在很难区分一个NULL是表示正常的分组值还是超级聚合值。

什么是MySQL-8.0.1中的新功能

在上述示例中可以使用GROUPING函数来区分由ROLLUP产生的NULL和NULL与分组数据。 当该列生成的NULL是ROLLUP操作的结果时，列的GROUPING函数返回值为1。 否则返回值为0。mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a,b) FROM t1 GROUP BY a,b WITH ROLLUP;

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

| a    | b    | SUM  | GROUPING(a,b) |

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

| NULL |  112 | NULL |             0 |

| NULL | NULL | NULL |             1 |

|  111 |   11 |   11 |             0 |

|  111 |   12 |   12 |             0 |

|  111 | NULL |   23 |             1 |

|  222 |   22 |   22 |             0 |

|  222 |   23 |   23 |             0 |

|  222 | NULL |   45 |             1 |

| 1111 | NULL |  112 |             0 |

| 1111 | NULL |  112 |             1 |

| NULL | NULL |  180 |             3 |

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

11 rows in set (0.00 sec)

如这里所示，如果GROUPING(a，b)返回3，则表示该列的列“a”中的NULL和列“b”中的NULL由ROLLUP操作生成。 如果结果为1，则列“b”中的NULL只是ROLLUP操作的结果。

GROUPING功能的其他用途

我们可以在选择列表或有条件中指定GROUPING函数。 在具有条件的情况下指定时，我们可以使用此函数仅检索超级聚合行或仅汇总行，如下面的示例。mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1;

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

| a    | b    | SUM  |

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

| NULL | NULL | NULL |

|  111 | NULL |   23 |

|  222 | NULL |   45 |

| 1111 | NULL |  112 |

| NULL | NULL |  180 |

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

5 rows in set (0.00 sec)

我们也可以使用GROUPING函数来区分超级聚合和聚合，如下所示：mysql> SELECT IF(GROUPING(a)=1,'All Departments', a) as Department, IF(GROUPING(b)=1, 'All Employees', b) as Employees, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;

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

| Department      | Employees     | SUM  |

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

| NULL            | 112           | NULL |

| NULL            | All Employees | NULL |

| 111             | 11            |   11 |

| 111             | 12            |   12 |

| 111             | All Employees |   23 |

| 222             | 22            |   22 |

| 222             | 23            |   23 |

| 222             | All Employees |   45 |

| 1111            | NULL          |  112 |

| 1111            | All Employees |  112 |

| All Departments | All Employees |  180 |

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

11 rows in set (0.00 sec)

这些是MySQL服务器中新增的GROUPING功能的一些用途。