一、基本概念
Oracle分组查询指的是将一张表按照某个字段或几个字段进行分组,并在每个组内进行聚合运算,得到结果集。分组查询最常见的聚合运算包括:计数、求和、平均数、最大值和最小值。通过分组查询,我们可以更好地理解数据,进行数据分析。
二、分组查询的语法
分组查询的语法基本如下:
SELECT column1, column2, COUNT(column3) FROM table_name GROUP BY column1, column2;
其中,column1
和column2
指定了要进行分组的字段,COUNT(column3)
指定了要进行聚合运算的字段,table_name
是表名。需要注意的是,分组查询的结果集中只包含SELECT
子句中的聚合函数、分组字段以及常量,在GROUP BY
子句中未出现的列不允许出现在SELECT
子句中。
三、基本的分组查询示例
我们通过一个简单的示例来说明分组查询的用法。
假设我们有以下一张员工表:
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO ------+--------+-----------+---------+-----------+--------+------+------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800.00 | | 20 7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 | 1600.00| 300 | 30 7521 | WARD | SALESMAN | 7698 | 22-FEB-81 | 1250.00| 500 | 30 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975.00| | 20 7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 | 1250.00| 1400 | 30 7698 | BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850.00| | 30 7782 | CLARK | MANAGER | 7839 | 09-JUN-81 | 2450.00| | 10 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000.00| | 20 7839 | KING | PRESIDENT| | 17-NOV-81 | 5000.00| | 10 7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 | 1500.00| 0 | 30 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100.00| | 20 7900 | JAMES | CLERK | 7698 | 03-DEC-81 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000.00| | 20 7934 | MILLER | CLERK | 7782 | 23-JAN-82 | 1300.00| | 10
我们可以按照部门DEPTNO
进行分组查询,统计各部门的平均工资:
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
以上命令将返回以下结果:
DEPTNO | AVG(SAL) ------+--------- 10 | 2916.67 20 | 2175.00 30 | 1566.67
另外,我们也可以按照职位JOB
进行分组查询,统计各职位的平均工资:
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
以上命令将返回以下结果:
JOB | AVG(SAL) -------------+--------- ANALYST | 3000.00 CLERK | 1157.14 MANAGER | 2758.33 PRESIDENT | 5000.00 SALESMAN | 1400.00
四、分组查询的having子句
在前面的示例中,我们仅仅是对数据进行了分组查询,并且使用了聚合函数进行了统计。然而,我们还可以使用HAVING
子句对分组结果进行过滤。
HAVING
子句的用法和WHERE
子句是类似的,区别在于HAVING
子句是对分组结果进行过滤,而WHERE
子句是对单行记录进行过滤。
以下是一个示例:
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) > 2000;
以上命令将返回以下结果:
DEPTNO | AVG(SAL) ------+--------- 10 | 2916.67
在以上命令中,我们使用HAVING AVG(SAL) > 2000
对分组结果进行过滤,只返回平均工资大于2000的部门。
五、分组查询中的排序
我们可以在分组查询的结果中进行排序。排序的语法如下:
SELECT column1, column2, ... FROM table_name GROUP BY column1, column2, ... ORDER BY column1, column2, ... [ASC|DESC];
其中,column1
、column2
等指定了要进行分组的字段,table_name
是表名,ORDER BY
指定了排序的方式(默认为升序,如果需要降序可以使用DESC
选项)。
以下是一个示例:
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL) DESC;
以上命令将返回以下结果:
DEPTNO | AVG(SAL) ------+--------- 10 | 2916.67 20 | 2175.00 30 | 1566.67
在以上命令中,我们按照平均工资进行降序排序。
六、分组查询的高级用法
在实际工作中,我们可能需要更加复杂的分组查询。下面介绍几种分组查询的高级用法。
1. 多个聚合函数同时使用
我们可以在一个分组查询中使用多个聚合函数,并且对不同的字段进行聚合:
SELECT DEPTNO, AVG(SAL), COUNT(*) FROM EMP GROUP BY DEPTNO;
以上命令将返回以下结果:
DEPTNO | AVG(SAL) | COUNT(*) ------+----------+--------- 10 | 2916.670| 3 20 | 2175.000| 5 30 | 1566.670| 6
在以上命令中,我们同时使用了平均工资和记录数这两个聚合函数,分别对SAL
和*
进行了聚合计算。
2. 连接查询和分组查询结合使用
我们可以使用连接查询和分组查询结合使用。以下是一个基本的示例:
SELECT DEPT.DNAME, AVG(EMP.SAL) FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO GROUP BY DEPT.DNAME;
以上命令将返回以下结果:
DNAME | AVG(SAL) --------+--------- ACCOUNTING| 2916.67 RESEARCH | 2175.00 SALES | 1566.67
在以上示例中,我们将EMP
表和DEPT
表连接起来,然后按照部门名称进行分组查询,并计算出平均工资。
3. 分组查询和子查询结合使用
我们还可以使用子查询和分组查询结合使用。以下是一个基本的示例:
SELECT column1, column2, ... FROM table_name WHERE column1 IN ( SELECT column1 FROM table_name GROUP BY column1 HAVING COUNT(*) > 1 );
在以上示例中,我们首先进行子查询,找出所有出现了两次及以上的column1
,然后通过IN
运算符进行过滤,筛选出相应的记录。
七、总结
本文从基本概念、语法、示例以及高级用法等方面详细阐述了Oracle分组查询的使用方法。分组查询是数据分析和统计的重要手段,对于我们理解数据、进行数据分析都有非常重要的帮助。