一、概述

數(shù)據(jù)分組的目的是用來匯總數(shù)據(jù)或為整個分組顯示單行的匯總信息，通常在查詢結(jié)果集中使用GROUP BY 子句對記錄進行分組。在SELECT 語句中，GROUP BY 子句位于FROM 子句之后，語法格式：

SELECT columns_list
FROM table_name
[WHERE conditional_expression]
GROUP BY columns_list

GROUP BY 子句可以基于指定某一列的值將數(shù)據(jù)集合劃分為多個分組，同一組內(nèi)所有記錄在分組屬性上具有相同值，也可以基于指定多列的值將數(shù)據(jù)集合劃分為多個分組。 

二、分組查詢的幾種情況

1、使用GROUP BY子句進行單列分組

單列分組是指基于列生成分組統(tǒng)計結(jié)果。當進行單列分組時，會基于分組列的每個不同值生成一個統(tǒng)計結(jié)果。

【例1.1】在EMP表中，按照部門編號（deptno）和職務(wù)列進行分組。

select deptno,job from emp group by deptno,job order by deptno

 group by 子句經(jīng)常與聚集函數(shù)一起使用。使用group by 子句和聚集函數(shù)，可以實現(xiàn)對查詢結(jié)果中每一組數(shù)據(jù)進行分類統(tǒng)計。所以，在結(jié)果中每組數(shù)據(jù)都有一個與之對應(yīng)的統(tǒng)計值。

【例1.2】在emp表中，使用group by 對工資記錄進行分組，并計算平均工資（avg）、所有工資的總和(sum)、最高工資(max)和各組的行數(shù)（count）

select avg(sal) 平均工資,sum(sal) 工資總和,max(sal) 最高工資,count(job) 行數(shù)
from emp  group by job

注意：

1、在select 子句的后面只可以有兩類表達式：統(tǒng)計函數(shù)和進行分組的列名。

2、select子句中的列必須是進行分組的列，除此之外添加其他的列名都是錯誤的，但是group by子句后面的列名可以不出現(xiàn)在select子句中。

3、在默認情況下，將按照group by子句指定的分組列升序排列，如果需要重新排序，可以使用order  by 子句指定新的排列順序。

group by 子句中的列可以不再select列表中。

【例1.3】查詢emp表，顯示按職位-job分類的每類員工的平均工資，并且顯示的結(jié)果按照職位有小到大排列。

select avg(sal) 平均工資 from emp  group by job

 從上面的運行結(jié)果很難看出這一結(jié)果是按什么排序的。為了提高程序的可讀性，應(yīng)盡可能不使用這樣的查詢方法。實際的使用查詢方法如下：

select job,avg(sal) 平均工資 from emp  group by job

2、使用group by 子句進行多列分組

多列分組是指基于兩個或另個以上的列生成分組統(tǒng)計結(jié)果。當進行多列分組時，會基于多個列的不同值生成統(tǒng)計結(jié)果。

【2.1】使用group by 進行多列分組，查詢emp表，顯示每個部門每種崗位的平均工資和最高工資。

select deptno,job,avg(sal) 平均工資,max(sal) 最高工資  from emp group by deptno,job

3、使用order by 子句改變分組排序結(jié)果

當使用group by 子句執(zhí)行分組統(tǒng)計時，會自動基于分組列進行升序排列。為了改變分組數(shù)據(jù)的排序結(jié)果，需要使用order by 子句。

【例3.1】查詢emp表，顯示每個部門的部門號及工資總額，并按照工資總額降序排列。 

select deptno,sum(sal)  from emp 
where deptno is not null
group by deptno 
order by sum(sal) desc;

4、使用HAVING子句限制分組結(jié)果

having 子句通常與group by 子句一起使用，在完成對分組結(jié)果統(tǒng)計后，可以使用having 子句對分組的結(jié)果做進一步篩選。如果不使用group by 子句，having子句的功能與where一樣。having子句與where的相似之處都是定義搜索條件。唯一不同的是having子句中可以包含聚合函數(shù)，如count,avg,sum等，在where子句中則不可以使用聚合函數(shù)。

如果在select語句中使用了group by 子句，那么having子句應(yīng)用于group by 子句創(chuàng)建的那些組。如果執(zhí)行了where子句，而沒有指定group by 子句，那么having 子句應(yīng)用于where子句的輸出，并且整個輸出被看作一個組，如果select 語句中既沒有指定where，也沒有指定group by ,那么having子句將應(yīng)用于from 子句的輸出，并且將其看作一個組。

提示：

對于having子句作用的理解有一個辦法，就是記住select 語句中子句處理順序。在select 語句中，首先由from 子句找到數(shù)據(jù)表，where 子句則接收from 子句輸出的數(shù)據(jù)，而having 子句則接收來自group by 、where 或 from 子句的輸出。

【例4.1】在emp表中，首先通過分組的方式計算出每個部門的平均工資，然后在通過having子句過濾出平均 工資大于2000的記錄信息。

select deptno 部門編號 ,avg(sal) 平均工資 from emp 
group by deptno 
having avg(sal) > 2000

從上面的查詢結(jié)果中可以看出，select語句使用group by 子句對emp表進行分組統(tǒng)計，然后再由having子句根據(jù)統(tǒng)計值進一步篩選。

上面的例子無法使用where子句直接過濾出平均工資大于2000的部門信心，因為where 子句不能使用聚合函數(shù)。

通常情況下，having與group by 一起使用，這樣可以匯總相關(guān)數(shù)據(jù)后在進一步篩選匯總的數(shù)據(jù)。

5、在group by 子句中使用rollup 和cube操作符

5.1 使用ROLLUP 操作符執(zhí)行數(shù)據(jù)統(tǒng)計

當直接使用group by子句進行多列分組時，只能生成簡單的數(shù)據(jù)統(tǒng)計結(jié)果。為了生成數(shù)據(jù)統(tǒng)計、橫向小計和總計統(tǒng)計，可以在group by 使用rollup操作符。

【例5.1.1】在emp表中，使用rollup操作符，顯示各部門每個崗位的平均工資、每部門的平均工資、雇員的平均工資。

select deptno 部門編號 ,job as 崗位,avg(sal) 平均工資 from emp 
where sal is not null
group by rollup(deptno,job)

5.2 使用cubr操作符執(zhí)行數(shù)據(jù)統(tǒng)計

為了生成數(shù)據(jù)統(tǒng)計、橫向小計、縱向小計以及總計統(tǒng)計，可以使用cube操作符。

【5.2.1】在emp表中，使用cube操作符，顯示各部門各崗位的平均工資、部門平局工資、崗位平均工資、所有雇員平均工資。

select deptno 部門編號 ,job as 崗位,avg(sal) 平均工資 from emp 
where sal is not null
group by cube(deptno,job)

附：分組查詢的高級用法

在實際工作中，我們可能需要更加復(fù)雜的分組查詢。下面介紹幾種分組查詢的高級用法。

1. 多個聚合函數(shù)同時使用

我們可以在一個分組查詢中使用多個聚合函數(shù)，并且對不同的字段進行聚合：

SELECT DEPTNO, AVG(SAL), COUNT(*)
FROM EMP
GROUP BY DEPTNO;

以上命令將返回以下結(jié)果：

DEPTNO | AVG(SAL) | COUNT(*)
------+----------+---------
10    | 2916.670|        3
20    | 2175.000|        5
30    | 1566.670|        6

在以上命令中，我們同時使用了平均工資和記錄數(shù)這兩個聚合函數(shù)，分別對SAL和*進行了聚合計算。

2. 連接查詢和分組查詢結(jié)合使用

我們可以使用連接查詢和分組查詢結(jié)合使用。以下是一個基本的示例：

SELECT DEPT.DNAME, AVG(EMP.SAL)
FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY DEPT.DNAME;

以上命令將返回以下結(jié)果：

DNAME   | AVG(SAL)
--------+---------
ACCOUNTING| 2916.67
RESEARCH  | 2175.00
SALES     | 1566.67

在以上示例中，我們將EMP表和DEPT表連接起來，然后按照部門名稱進行分組查詢，并計算出平均工資。

3. 分組查詢和子查詢結(jié)合使用

我們還可以使用子查詢和分組查詢結(jié)合使用。以下是一個基本的示例：

SELECT column1, column2, ...
FROM table_name
WHERE column1 IN (
  SELECT column1
  FROM table_name
  GROUP BY column1
  HAVING COUNT(*) > 1
);

在以上示例中，我們首先進行子查詢，找出所有出現(xiàn)了兩次及以上的column1，然后通過IN運算符進行過濾，篩選出相應(yīng)的記錄。

總結(jié) 

到此這篇關(guān)于Oracle基礎(chǔ)教程之分組查詢的文章就介紹到這了,更多相關(guān)Oracle分組查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論