您的位置:

深入理解Oracle分组查询

一、基本概念

Oracle分组查询指的是将一张表按照某个字段或几个字段进行分组,并在每个组内进行聚合运算,得到结果集。分组查询最常见的聚合运算包括:计数、求和、平均数、最大值和最小值。通过分组查询,我们可以更好地理解数据,进行数据分析。

二、分组查询的语法

分组查询的语法基本如下:

SELECT column1, column2, COUNT(column3)
FROM table_name
GROUP BY column1, column2;

其中,column1column2指定了要进行分组的字段,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];

其中,column1column2等指定了要进行分组的字段,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分组查询的使用方法。分组查询是数据分析和统计的重要手段,对于我们理解数据、进行数据分析都有非常重要的帮助。