GROUP BY子句
📄字数 3.3K
👁️阅读量 加载中...
一、分组查询
分组子句关键字为GROUP BY，通过GROUP BY指定的规则将数据划分为若干组，再对每一组的数据进行处理，分组子句通常配合聚合函数进行统计数据。GROUP BY指定的规则即字段信息可以包含FROM子句中的表、视图的任何列，若查询仅返回聚合函数结果则GROUP BY指定的列可以不在选择列表中，若查询返回聚合函数结果和其他字段信息则GROUP BY指定的列必须在返回的选择列表中。
GROUP BY扩展功能：
ROLLUP：n+1次分组统计，其中n为分组字段数，按照从右至左依次递减字段生成分组统计，即对每个分组结果再生成小计和总和。如ROLLUP(a,b)的分组为(a,b)、(a)、()三种情况，第一次将整体作为条件进行分组小计，第二次对分组统计的最后一个字段删减作为条件再进行分组小计，第三次再次从右至左删减字段作为条件进行分组小计，即为无条件将整个表进行统计。CUBE：多字段分组时按照聚合字段排列组合进行分组统计，并对每一种组合结果进行汇总统计。如CUBE(a,b)统计的分组为(a,b)、(a)、(b)、()情况。GROUPING SETS：无需所有分组字段的排列组合，仅返回每个字段的分组小计。如GROUPING SETS(a,b)统计的分组为(a)、(b)情况。
语法格式请参见查询语法select章节。
1.1 语法格式
1.2 参数说明
GROUP BY group_item (',' group_item)*：表示GROUP BY子句后面跟随一个或多个group_item，每个group_item之间用逗号分隔。b_expr：表示一个简单的表达式，通常是列名或基于列的表达式。这是最常见的分组项形式。ROLLUP：GROUP BY的扩展，n+1次分组统计，其中n为分组字段数，按照从右至左依次递减字段生成分组统计，即对每个分组结果再进行小计和总和。如ROLLUP(a,b)的分组为(a,b)、(a)、()三种情况，第一次将整体作为条件进行分组小计，第二次对分组统计的最后一个字段删减作为条件再进行分组小计，第三次再次从右至左删减字段作为条件进行分组小计即为无条件将整个表进行统计。CUBE：GROUP BY的扩展，多字段分组时按照聚合字段排列组合进行分组统计并对每一种组合结果进行汇总统计。如CUBE(a,b)统计的分组为(a,b)、(a)、(b)、()情况。GROUPING SETS：GROUP BY的扩展，无需所有分组字段的排列组合仅返回每个字段的分组小计。如GROUPING SETS(a,b)统计的分组为(a)、(b)情况。group_item (',' group_item)*：多个group_item的组合，类似于直接在GROUP BY子句中列出多个分组项。()：空分组项，对整个表进行汇总，不分组。
1.3 示例
sql
-- 无指定字段返回
SQL> CREATE TABLE tab_grp1(id INT,name VARCHAR(20));
Execute successful.
Use time:37 ms.
SQL> INSERT INTO tab_grp1 VALUES(1,'abc')(2,'def')(1,'one');
Total 3 records effected.
Use time:1 ms.
SQL> SELECT COUNT(name) FROM tab_grp1 GROUP BY id;
+-------+
| EXPR1 |
+-------+
| 2 |
| 1 |
+-------+
(2 rows)
Use time:3 ms.
-- GROUP BY指定字段在返回列表字段中。
SQL> SELECT COUNT(name),id FROM tab_grp1 GROUP BY id;
+-------+----+
| EXPR1 | ID |
+-------+----+
| 2 | 1 |
| 1 | 2 |
+-------+----+
(2 rows)
Use time:0 ms.
-- GROUP BY的三种扩展方式
SQL> CREATE TABLE tab_grp2(id INT PRIMARY KEY,name VARCHAR(15),department VARCHAR(10),salary NUMBER(8, 2),gender VARCHAR(10));
Execute successful.
Use time:38 ms.
SQL> INSERT INTO tab_grp2 VALUES (1001, 'John', 'IT', 35000, 'Male')(1002, 'Smith', 'HR', 45000, 'Male')(1003, 'James', 'Finance', 50000, 'Male')(1004, 'Mike', 'Finance', 50000, 'Male')(1005, 'Linda', 'HR', 75000, 'Female')(1006, 'Anurag', 'IT', 35000, 'Male')(1007, 'Priyanla', 'HR', 45000, 'Female')(1008, 'Sambit', 'IT', 55000, 'Female')(1009, 'Pranaya', 'IT', 57000, 'Female')(1010, 'Hina', 'HR', 75000, 'Male')(1011, 'Warner', 'Finance', 55000, 'Female');
Total 11 records effected.
Use time:0 ms.
-- 仅GROUP BY
SQL> SELECT department,gender,COUNT(*) group_cou FROM tab_grp2 GROUP BY(department,gender);
+------------+--------+-----------+
| DEPARTMENT | GENDER | GROUP_COU |
+------------+--------+-----------+
| Finance | Female | 1 |
| Finance | Male | 2 |
| HR | Female | 2 |
| HR | Male | 2 |
| IT | Female | 2 |
| IT | Male | 2 |
+------------+--------+-----------+
(6 rows)
Use time:1 ms.
-- ROLLUP
SQL> SELECT department,gender,COUNT(*) rollup_cou FROM tab_grp2 GROUP BY ROLLUP(department,gender);
+------------+--------+------------+
| DEPARTMENT | GENDER | ROLLUP_COU |
+------------+--------+------------+
| Finance | Female | 1 |
| Finance | Male | 2 |
| Finance | <NULL> | 3 |
| HR | Female | 2 |
| HR | Male | 2 |
| HR | <NULL> | 4 |
| IT | Female | 2 |
| IT | Male | 2 |
| IT | <NULL> | 4 |
| <NULL> | <NULL> | 11 |
+------------+--------+------------+
(10 rows)
Use time:0 ms.
-- CUBE
SQL> SELECT department,gender,COUNT(*) cube_cou FROM tab_grp2 GROUP BY CUBE(department,gender);
+------------+--------+----------+
| DEPARTMENT | GENDER | CUBE_COU |
+------------+--------+----------+
| Finance | Female | 1 |
| Finance | Male | 2 |
| Finance | <NULL> | 3 |
| HR | Female | 2 |
| HR | Male | 2 |
| HR | <NULL> | 4 |
| IT | Female | 2 |
| IT | Male | 2 |
| IT | <NULL> | 4 |
| <NULL> | <NULL> | 11 |
| <NULL> | Female | 5 |
| <NULL> | Male | 6 |
+------------+--------+----------+
(12 rows)
Use time:0 ms.
-- GROUPING SETS
SQL> SELECT department,gender,COUNT(*) group_set_cou FROM tab_grp2 GROUP BY GROUPING SETS(department,gender);
+------------+--------+---------------+
| DEPARTMENT | GENDER | GROUP_SET_COU |
+------------+--------+---------------+
| Finance | <NULL> | 3 |
| HR | <NULL> | 4 |
| IT | <NULL> | 4 |
| <NULL> | Female | 5 |
| <NULL> | Male | 6 |
+------------+--------+---------------+
(5 rows)
Use time:0 ms.二、分组过滤
HAVING是SQL中用于对分组（GROUP BY）后的结果进行条件过滤的子句，通常结合聚合函数一同使用。HAVING的作用类似于WHERE，但HAVING的过滤作用于分组之后，WHERE的过滤作用于分组之前，而且HAVING可以结合聚合函数一同使用，但WHERE不可以。
2.1 语法格式
2.2 参数说明
bool_expr：过滤条件的逻辑表达式，请参阅逻辑表达式。
2.3 示例
sql
SQL> CREATE TABLE tab_having (id INT,name VARCHAR2(50),department VARCHAR2(50));
Execute successful.
Use time:31 ms.
SQL> INSERT INTO tab_having VALUES (1, 'Alice', 'HR')(2, 'Bob', 'HR')(3, 'Carol', 'HR')(4, 'David', 'IT')(5, 'Eve', 'IT')(6, 'Frank', 'Finance');
Total 6 records effected.
Use time:0 ms.
-- 使用聚合函数进行过滤
SQL> SELECT department, COUNT(*) AS emp_count FROM tab_having GROUP BY department HAVING COUNT(*) > 2;
+------------+-----------+
| DEPARTMENT | EMP_COUNT |
+------------+-----------+
| HR | 3 |
+------------+-----------+
(1 row)
Use time:2 ms.
-- 使用查询结果进行过滤
SQL> SELECT department, COUNT(*) AS emp_count FROM tab_having GROUP BY department HAVING emp_count > 2;
+------------+-----------+
| DEPARTMENT | EMP_COUNT |
+------------+-----------+
| HR | 3 |
+------------+-----------+
(1 row)
Use time:3 ms.