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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類