Oracle基礎(chǔ)教程之分組查詢
一、概述
數(shù)據(jù)分組的目的是用來匯總數(shù)據(jù)或?yàn)檎麄€分組顯示單行的匯總信息,通常在查詢結(jié)果集中使用GROUP BY 子句對記錄進(jìn)行分組。在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子句進(jìn)行單列分組
單列分組是指基于列生成分組統(tǒng)計結(jié)果。當(dāng)進(jìn)行單列分組時,會基于分組列的每個不同值生成一個統(tǒng)計結(jié)果。
【例1.1】在EMP表中,按照部門編號(deptno)和職務(wù)列進(jìn)行分組。
select deptno,job from emp group by deptno,job order by deptno
group by 子句經(jīng)常與聚集函數(shù)一起使用。使用group by 子句和聚集函數(shù),可以實(shí)現(xiàn)對查詢結(jié)果中每一組數(shù)據(jù)進(jìn)行分類統(tǒng)計。所以,在結(jié)果中每組數(shù)據(jù)都有一個與之對應(yīng)的統(tǒng)計值。
【例1.2】在emp表中,使用group by 對工資記錄進(jìn)行分組,并計算平均工資(avg)、所有工資的總和(sum)、最高工資(max)和各組的行數(shù)(count)
select avg(sal) 平均工資,sum(sal) 工資總和,max(sal) 最高工資,count(job) 行數(shù) from emp group by job
注意:
1、在select 子句的后面只可以有兩類表達(dá)式:統(tǒng)計函數(shù)和進(jìn)行分組的列名。
2、select子句中的列必須是進(jìn)行分組的列,除此之外添加其他的列名都是錯誤的,但是group by子句后面的列名可以不出現(xiàn)在select子句中。
3、在默認(rèn)情況下,將按照group by子句指定的分組列升序排列,如果需要重新排序,可以使用order by 子句指定新的排列順序。
group by 子句中的列可以不再select列表中。
【例1.3】查詢emp表,顯示按職位-job分類的每類員工的平均工資,并且顯示的結(jié)果按照職位有小到大排列。
select avg(sal) 平均工資 from emp group by job
從上面的運(yùn)行結(jié)果很難看出這一結(jié)果是按什么排序的。為了提高程序的可讀性,應(yīng)盡可能不使用這樣的查詢方法。實(shí)際的使用查詢方法如下:
select job,avg(sal) 平均工資 from emp group by job
2、使用group by 子句進(jìn)行多列分組
多列分組是指基于兩個或另個以上的列生成分組統(tǒng)計結(jié)果。當(dāng)進(jìn)行多列分組時,會基于多個列的不同值生成統(tǒng)計結(jié)果。
【2.1】使用group by 進(jìn)行多列分組,查詢emp表,顯示每個部門每種崗位的平均工資和最高工資。
select deptno,job,avg(sal) 平均工資,max(sal) 最高工資 from emp group by deptno,job
3、使用order by 子句改變分組排序結(jié)果
當(dāng)使用group by 子句執(zhí)行分組統(tǒng)計時,會自動基于分組列進(jìn)行升序排列。為了改變分組數(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é)果做進(jìn)一步篩選。如果不使用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表進(jìn)行分組統(tǒng)計,然后再由having子句根據(jù)統(tǒng)計值進(jìn)一步篩選。
上面的例子無法使用where子句直接過濾出平均工資大于2000的部門信心,因?yàn)閣here 子句不能使用聚合函數(shù)。
通常情況下,having與group by 一起使用,這樣可以匯總相關(guān)數(shù)據(jù)后在進(jìn)一步篩選匯總的數(shù)據(jù)。
5、在group by 子句中使用rollup 和cube操作符
5.1 使用ROLLUP 操作符執(zhí)行數(shù)據(jù)統(tǒng)計
當(dāng)直接使用group by子句進(jìn)行多列分組時,只能生成簡單的數(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)
附:分組查詢的高級用法
在實(shí)際工作中,我們可能需要更加復(fù)雜的分組查詢。下面介紹幾種分組查詢的高級用法。
1. 多個聚合函數(shù)同時使用
我們可以在一個分組查詢中使用多個聚合函數(shù),并且對不同的字段進(jìn)行聚合:
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
和*
進(jìn)行了聚合計算。
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
表連接起來,然后按照部門名稱進(jìn)行分組查詢,并計算出平均工資。
3. 分組查詢和子查詢結(jié)合使用
我們還可以使用子查詢和分組查詢結(jié)合使用。以下是一個基本的示例:
SELECT column1, column2, ... FROM table_name WHERE column1 IN ( SELECT column1 FROM table_name GROUP BY column1 HAVING COUNT(*) > 1 );
在以上示例中,我們首先進(jìn)行子查詢,找出所有出現(xiàn)了兩次及以上的column1
,然后通過IN
運(yùn)算符進(jìn)行過濾,篩選出相應(yīng)的記錄。
總結(jié)
到此這篇關(guān)于Oracle基礎(chǔ)教程之分組查詢的文章就介紹到這了,更多相關(guān)Oracle分組查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Oracle中查詢表結(jié)構(gòu)的6種方法總結(jié)
工作中查看oracle表結(jié)構(gòu)經(jīng)常會遇到,下面這篇文章主要給大家介紹了關(guān)于Oracle中查詢表結(jié)構(gòu)的6種方法,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-04-04DBA 在Linux下安裝Oracle Database11g數(shù)據(jù)庫圖文教程
正在學(xué)習(xí)Oracle DBA的知識,所以安裝oracle 11個的數(shù)據(jù)庫用以做測試,如Clone, RMAN, Stream等2014-08-08PLSQL?Developer安裝詳細(xì)步驟及?plsql?Developer?14注冊碼
這篇文章主要介紹了PLSQL?Developer安裝詳細(xì)步驟,文末給大家提到了PL/SQL?plsql?Developer?14最新版注冊碼,需要的朋友可以參考下2022-03-03oracle數(shù)據(jù)庫在客戶端建立dblink語法
oracle服務(wù)器沒有建立目標(biāo)數(shù)據(jù)庫的TNS時,在客戶端(有權(quán)限的情況下)建立dblink語法如下,有需求的朋友可以參考下哈2013-05-05Oracle(90)數(shù)據(jù)庫如何創(chuàng)建用戶(User)
這篇文章主要介紹了在Oracle數(shù)據(jù)庫中創(chuàng)建用戶的過程,包括連接到數(shù)據(jù)庫、創(chuàng)建用戶、分配權(quán)限、分配表空間和設(shè)置賬戶狀態(tài),提供了詳細(xì)的步驟和代碼示例,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-12-12