--- title: "GROUPING - OceanBase 数据库 V4.3.1 | OceanBase 文档中心" description: GROUPING 声明 GROUPING(expr) 说明 GROUP BY 可以通过 WITH ROLLUP 子句将各分组进行汇总以表示更高级的聚合（也称为超聚合），并产生额外的行。例如以下 SQL 可以将销售额按年度分组，并输出各分组的汇总。 其中 year 为 NULL 的行即超聚合行。 obclient> S… --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 4.3.1 # GROUPING 更新时间：2026-04-10 11:58:01 [编辑](https://github.com/oceanbase/oceanbase-doc/edit/V4.3.1/zh-CN/700.reference/500.sql-reference/100.sql-syntax/200.common-tenant-of-mysql-mode/400.functions-of-mysql-mode/300.aggregate-function-of-mysql-mode/800.grouping-agg-of-mysql-mode.md) ## 声明 ```sql GROUPING(expr) ``` ## 说明 `GROUP BY` 可以通过 `WITH ROLLUP` 子句将各分组进行汇总以表示更高级的聚合（也称为超聚合），并产生额外的行。例如以下 SQL 可以将销售额按年度分组，并输出各分组的汇总。 其中 `year` 为 `NULL` 的行即超聚合行。 ```sql obclient> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+--------+ ``` `WITH ROLLUP` 修饰多个 `GROUP BY` 列时，结果会更加复杂。在这种情况下，每一个分组都会产生一个超聚合行。例如以下 SQL 会产生额外的超聚合行。 ```sql obclient> SELECT year, country, product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP; +------+---------+------------+--------+ | year | country | product | profit | +------+---------+------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | NULL | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | NULL | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+--------+ ``` `GROUPING` 函数用于对超聚合行进行区分，如果返回的行是超聚合行，则该函数返回 `1`，否则返回 `0`。 例如对上述 SQL 进行改写，利用 GROUPING 函数的特性添加判断。 ```sql obclient> SELECT IF(GROUPING(year), 'All years', year) AS year, IF(GROUPING(country), 'All countries', country) AS country, IF(GROUPING(product), 'All products', product) AS product, SUM(profit) AS profit FROM sales GROUP BY year, country, product WITH ROLLUP; +-----------+---------------+--------------+--------+ | year | country | product | profit | +-----------+---------------+--------------+--------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | All products | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | All products | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | All products | 1575 | | 2000 | All countries | All products | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | All products | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | All products | 3000 | | 2001 | All countries | All products | 3010 | | All years | All countries | All products | 7535 | +-----------+---------------+--------------+--------+ ``` ## 示例 ```sql obclient> CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT); INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700); INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000); INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400); INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000); INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700); INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500); INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000); INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000); INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500); INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000); INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000); COMMIT; obclient> SELECT IF(GROUPING(manager_id),'manager_id',manager_id) AS manager_id,SUM(SALARY) FROM employees GROUP BY manager_id WITH ROLLUP; +------------+-------------+ | manager_id | SUM(SALARY) | +------------+-------------+ | 100 | 70300 | | 200 | 40500 | | manager_id | 110800 | +------------+-------------+ 3 rows in set ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线