深入理解Oracle分組查詢

一、基本概念

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分組查詢的使用方法。分組查詢是數據分析和統計的重要手段，對於我們理解數據、進行數據分析都有非常重要的幫助。