Oracle資料庫從入門到精通-分組統計查詢

来源:https://www.cnblogs.com/launolife/archive/2018/07/28/9383104.html
-Advertisement-
Play Games

視頻課程：李興華 Oracle從入門到精通 視頻課程學習者：陽光羅諾 視頻來源：51CTO學院 整體內容： 統計函數 在之前我們就學習過一個COUNT()函數，這個函數的主要作用是統計一張表之中的數據量的個數。和它功能與之類似的常用函數有五個： 統計個數COUNT()：根據表中的實際數據量返回結果。 ...


視頻課程：李興華 Oracle從入門到精通

視頻課程學習者：陽光羅諾

視頻來源：51CTO學院

整體內容：

  1. 統計函數的使用
  2. 分組統計查詢的實現
  3. 對分組的數據過濾

 

統計函數

 

在之前我們就學習過一個COUNT()函數，這個函數的主要作用是統計一張表之中的數據量的個數。和它功能與之類似的常用函數有五個：

  • 統計個數COUNT()：根據表中的實際數據量返回結果。
  •  求和SUM()：是針對於數字的統計
  • 平均值AVG()：是針對數字的統計
  •  最小值MIN()：各種數據類型都支持。
  • 最大值MAX():各種數據類型都支持。

 

範例：驗證各個函數。

代碼示例：

1 SELECT COUNT(*) 人數, AVG(sal)員工平均工資,SUM(sal)每月總支出,MAX(sal) 最高工資, MIN(sal)最低工資 
2 
3 FROM emp;

 

 

但是這些函數是允許和其他的函數嵌套的。

 

範例：統計出公司的平均雇佣年限。

代碼示例：

1 SELECT AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12) 
2 FROM emp;

 

範例：求出最早和最晚的雇佣日期（找到公司最早雇佣的雇員，以及公司最近雇佣的雇員日期）

 

代碼示例：

1 SELECT MAX(hiredate) 最晚，MIN(hiredate) 最早 FROM emp;

 

以上的幾個操作函數，在表中沒有數據的時候，只有COUNT()函數會返回結果，其他的都是null。

 

範例：統計Bonus表

代碼示例：

1 SELECT COUNT(*) 人數, AVG(sal)員工平均工資,SUM(sal)每月總支出,MAX(sal) 最高工資, MIN(sal)最低工資
2 
3 FROM bonus;

在圖中我們可以清楚的發現，此時只有COUNT()函數會返回最終的結果，即使沒有數據也會返回0，而其他的統計函數結果都是null。

        

　　實際上針對於COUNT()函數有三種使用形式：【面試題】

  • COUNT(*)：可以準確的返回表中的全部記錄數。
  • COUNT(欄位)：統計不為null的所有數據量。
  • COUNT(DISTINCT 欄位)：消除重覆數據之後的結果。

範例：統計查詢一

代碼示例：

1 SELECT COUNT(*),COUNT(empno),COUNT(comm) FROM emp;

 

範例：查詢二

代碼示例：

1 SELECT COUNT(DISTINCT JOB) FROM emp;

 

 

分組統計

分組的前提是存在有重覆，1但是允許單獨一行記錄進行分組。

如果要進行分組應該使用GROUP BY子句來完成，那麼此時的語法結構形式如下：

語法結構：

 

【④選出所需要的數據列】SELECT [DISTINCT] *  分組列[別名]，分組列[別名]，分組列[別名]······

【①確定數據來源（行和列的集合）】FROM 表名稱 [別名]，表名稱 [別名]，······

【②篩選數據行】[WHERE 限定條件]  此時的條件可以是多個語法結構。

【③針對於篩選的行分組】[GROUP BY 分組欄位，分組欄位，······]

【⑤數據排序】[ORDER BY 排序欄位 [ASC|DESC] 可以設置多個]

 

範例：根據部門編號分組，查詢出每一個部門的編號、人數、平均工資。

代碼示例：

1 SELECT deptno,count(*),avg(sal)
2 
3 FROM emp
4 
5 GROUP BY deptno;

 

 

範例：根據職位分組，統計出每一個職位的人數，最低工資與最高工資。

 

代碼示例：

1 SELECT job,count(*),MAX(sal),MIN(sal)
2 
3 FROM emp
4 
5 GROUP BY job;

查詢結果如圖：

 

在GROUP BY 子句中，之所以使用麻煩，是因為分組的時候有一些約定條件。

  • 如果查詢不適用GROUP BY子句，那麼在SELECT子句中只允許出現統計函數，其他任何欄位不允許出現。

錯誤代碼：

正確代碼：

SELECT empno,COUNT(*) FROM emp;

錯誤提示：

第 1 行出現錯誤:

ORA-00937: 不是單組分組函數

 

SELECT COUNT(*) FROM emp;

 

  • 如果查詢中使用了額GROUP BY子句，那麼SELECT子句中只允許出現分組欄位、統計函數，其他任何欄位都不允許出現。

錯誤代碼：

正確代碼：

SELECT ename,job,COUNT(*) FROM emp GROUP BY job;

錯誤提示：

第 1 行出現錯誤:

ORA-00979: 不是 GROUP BY 表達式

SELECT job,COUNT(*) FROM emp GROUP BY job;
  •  統計函數允許嵌套，但是嵌套之後的SELECT子句裡面只允許出現嵌套函數，而不允許任何欄位，包括分組欄位。

錯誤代碼：

正確代碼：

SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno;

錯誤提示：

第 1 行出現錯誤:

ORA-00937: 不是單組分組函數

SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;

 

 

 

 

 

多表查詢與分析統計（重點）

對於GROUP BY子句而言，是在WHERE子句之後執行的，所以在使用時可以進行限定查詢，也可以進行多表查詢。

 

範例：查詢出每個部門的名稱、部門人數、平均工資。

  • 確定要使用的數據表
      •        dept表：部門名稱
      • 　　emp表：統計數據
  • 確定已知的關聯欄位。
      • 　　　　雇員與部門：emp.deptno = dept.deptno;

第一步：查詢出每一個部門的名稱、雇員編號（COUNT(empno)）、基本工資（AVG(sal)）。

代碼示例：

1 SELECT d.dname,e.empno,e.sal
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno=d.deptno;

 

第二步：此時的查詢結果中對於部門名稱部分出現了重覆的內容，按照分組來講，只要是出現了數據的重覆，那麼就可以進行分組，只不過此時的分組是針對於臨時表（查詢結果），既然確定了dname上存在有重覆記錄，那麼就直接針對於dname分組即可。

代碼示例：

1 SELECT d.dname,COUNT(e.empno),AVG(e.sal)
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno=d.deptno
6 
7 GROUP BY d.dname;

 

第三步：在dept表中存在有四個部門信息，而此時的要求也是統計所有的部門名稱，如果發現數據不完整，立刻使用外連接。

代碼示例：

1 SELECT d.dname,COUNT(e.empno),AVG(e.sal)
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno=d.deptno(+)
6 
7 GROUP BY d.dname;

 

 

 

 

範例：查詢每個部門的編號、名稱、位置、部門人數、平均工資。

 

第一步：查詢每一個部門的編號、名稱、位置、雇員編號（COUNT()）、工資（AVG(sal)）.

代碼示例：

1 SELECT d.deptno,d.dname,d.loc,e.empno,e.sal
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno(+)=d.deptno;

 

 

 

第二步：此時發現三個列（dept列）同時發生著重覆，那麼就可以進行多欄位分組。

代碼示例：

1 SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)
2 
3 FROM emp e,dept d
4 
5 WHERE e.deptno(+)=d.deptno
6 
7 GROUP BY d.deptno,d.dname,d.loc;

 

 

 

HAVING子句

現在要求查詢出每個職位的名稱，職位的平均工資，但是要求顯示的職位的平均工資高於2000.

即：按照職位先進行分組，同時統計出每個職位的平均工資。隨後要求只顯示那些平均工資高於2000的職位信息，那麼既然現在要針對於顯示的數據進行篩選，自然就會首先想到WHERE子句，於是有瞭如下的代碼：

範例：代碼示例：

錯誤代碼：

SELECT job,AVG(sal)

FROM emp

WHERE AVG(sal)

GROUP BY job;

錯誤提示：

第 3 行出現錯誤:

ORA-00934: 此處不允許使用分組函數

 

此時直接告訴用戶，WHERE子句中不允許出現統計函數（分組函數）。因為GROUP BY子句在WHERE子句之後執行的。那麼此時執行WHERE子句時還沒有進行分組，那麼就自然無法進行統計。此時我們就可以使用HAVING子句來完成。

 

SQL語法結構：

【⑤選出所需要的數據列】SELECT [DISTINCT] *  分組列[別名]，分組列[別名]，分組列[別名]······

【①確定數據來源（行和列的集合）】FROM 表名稱 [別名]，表名稱 [別名]，······

【②篩選數據行】[WHERE 限定條件]  此時的條件可以是多個語法結構。

【③針對於篩選的行分組】[GROUP BY 分組欄位，分組欄位，······]

【③針對於篩選的行分組】[HAVING 分組過濾]

【⑥數據排序】[ORDER BY 排序欄位 [ASC|DESC] 可以設置多個]

 

範例：使用HAVING子句

代碼示例：

1 SELECT job,AVG(sal)
2 
3 FROM emp
4 
5 GROUP BY job
6 
7 HAVING AVG(sal)>2000;

 

 

HAVING實在GROUP BY分組之後才進行的篩選，在HAVING裡面可以直接使用統計函數。

 

說明：關於WHERE與HAVING的區別？

  1. WHERE子句在GROUP BY分組之前進行篩選，指的是選出那些可以參與分組的數據。並且在WHERE子句中不允許使用統計函數
  2. HAVING子句是在WHERE分組之後執行的，那麼就可以使用統計函數。

 

 

分組案例：

範例：顯示所有銷售人員的工作名稱以及從事同一個工作的雇員的月工資的總和，並且要求滿足從事同一工作的月工資的合計大於5000，顯示的結果按照月工資合計的升序排列。

 

 

第一步：查詢所有非銷售人員的信息，WHERE子句即可實現限定查詢。

 

代碼示例：

1 SELECT * FROM emp WHERE job<>'SALESMAN';

查詢結果：

 

 

第二步：按照職位進行分組，而後求出月工資的總支出。

代碼示例：

1 SELECT job,SUM(sal) 
2 FROM emp 
3 WHERE job<>'SALESMAN' 
4 GROUP BY job;

查詢結果：

 

 

第三步：分組後的數據進行再次的篩選，使用HAVING子句。

代碼示例：

1 SELECT job,SUM(sal) 
2 FROM emp 
3 WHERE job<>'SALESMAN' 
4 GROUP BY job 
5 HAVING SUM(sal)>5000;

 查詢結果：

 

 

第四步：按照月工資的合計升序排列。使用ORDER BY子句。

代碼示例：

 1 SELECT job,SUM(sal)
 2 
 3 FROM emp
 4 
 5 WHERE job<>'SALESMAN'
 6 
 7 GROUP BY job
 8 
 9 HAVING SUM(sal)>5000
10 
11 ORDER BY SUM(sal);

查詢結果：

 

 

範例：統計所有領取佣金和布領取佣金的人數、平均工資。

代碼示例：

1 SELECT comm,AVG(sal)
2 
3 FROM  emp
4 
5 GROUP BY comm;

查詢結果：

 

 

使用GROUP BY子句會把每一個種子值當作一個分組，所以此時不可能直接使用GROUP BY。

 

查詢出所有領取佣金的雇員的人數、平均工資。————直接使用WHERE子句。不需要使用GROUP BY子句

代碼示例：

1 SELECT '領取佣金' info，COUNT(*),AVG(sal) 
2 FROM emp 
3 WHERE comm IS NOT  NULL;

查詢結果：

 

查詢出所有不領取佣金的雇員的人數、平均工資。————直接使用WHERE子句。不需要使用GROUP BY子句

代碼示例：

1 SELECT '不領取佣金' info，COUNT(*),AVG(sal) 
2 FROM emp 
3 WHERE comm IS NULL;

查詢結果：

 

既然此時兩個查詢結果返回的結構完全相同，那麼我們就直接連接即可。

 

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • c# 讀取txt文檔和寫入文檔的方法
    StreamReader sr = new StreamReader(path); //path是要讀取的文件的完整路徑 String str_read = sr.ReadToEnd(); //從開始到末尾讀取文件的所有內容，str_read 存放的就是讀取到的文本 sr.Close(); //讀完 ...
  • Linux 安裝python3.7.0
    我這裡使用的時centos7-mini，centos系統本身預設安裝有python2.x，版本x根據不同版本系統有所不同，可通過 python --V 或 python --version 查看系統自帶的python版本 有一些系統命令時需要用到python2，不能卸載 1、安裝依賴包 1）首先安裝 ...
  • 徹底解決Linux索引節點（inode）占用率高的告警
    今天郵箱里發現有一封某伺服器inode使用率發生告警的郵件 登錄到伺服器上df i查看，發現/路徑下91%，磁碟使用率卻不高，猜測可能是某個目錄下的小文件過多，進而造成inode占用率過高，但不清楚根路徑下各文件夾里的文件數 於是乎執行以下命令，查看根路徑下各國文件夾的文件數 for i in / ...
  • Linux下源碼安裝並配置Nginx
    Linux下源碼安裝並配置Nginx，並將nginx命令添加到系統環境變數，將nginx添加到系統服務中 ...
  • CentOS搭建NAT和DHCP服務，實現共用上網
    NAT（Network address translation）即網路地址轉換，作為一種過渡解決手段，可以用來減少對全球合法IP地址的需求。簡單的說，NAT就是在內部專用網路中使用內部地址，而當內部節點要與外界網路發生聯繫時，就在邊緣路由器或者防火牆處，將內部地址轉換成全局地址，從而使得在外部公共網... ...
  • linux ----- Vim進入和退出命令
    Vim進入和退出命令 本來不想寫任何關於vim的文章的，無奈我今天又忘記怎麼退出vim了，常用命令是ESC，然後:wq（保存並退出），:q!(不保存並強制退出），i進入vim模式。另外還有其它的，我可能都不會用到。。。 按ESC鍵 跳到命令模式，然後： :w 保存文件但不退出vi :w file 將 ...
  • pl/sql developer開發工具的beautifier美化插件
    對於存儲過程中需要編寫大量的sql語句，這必然需要美化語句，使得程式可讀性更高。 pl/sql developer開發工具自帶美化工具，不過美化的時候容易使得語句全部改變成大寫格式，這樣就需要一個插件包或者說一個使得程式只格式不改變原來大小寫的文件包。 1、自帶美化，（自帶的不夠完美，不建議推薦使用 ...
  • MySQL中間件之ProxySQL(15)：ProxySQL代理MySQL組複製
    返回 "ProxySQL系列文章：http://www.cnblogs.com/f ck need u/p/7586194.html"   1.ProxySQL+組複製前言 在以前的ProxySQL版本中，要支持MySQL組複製(MGR,MySQL Group Replication)需要 ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類