SQL 基礎之組函數（九）

組函數:

– 類型和文法

– 使用 AVG, SUM, MIN, MAX, COUNT

– 組函數使用 DISTINCT 關鍵字

– 組函數中NULL 值

分組函數:作用于一組資料，并對一組資料傳回一個值

組函數類型

AVG 平均值

COUNT 統計值

MAX 最大值

MIN 最小值

SUM 合計

STDDEV 标準差

VARIANCE 方差

組函數文法：

select group_function(column), ... from table [where condition] [order by column];

使用 AVG 和和 SUM 函數

可以對數值型資料使用 AVG 和 SUM 函數

1、查詢job_id為REP的平均工資，最高工資，工資總和

select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%';

使用 MIN 和和 MAX

可以對數值型、字元型和日期型使用 MIN 和 MAX 函數

2、查詢入職時最短和最長時間

select min(hire_date),max(hire_date) from employees;

使用 COUNT

1、統計一下department_id 為50的部門有多少人

select count(*) from employees where department_id =50;

2、如果有空值不會被計算進去

select count(commission_pct) from employees where department_id=80;

3、顯示 EMPLOYEES 表中不同的部門數

select count(distinct department_id) from employees;

組函數忽略空值

1、統計一下提成

select avg(commission_pct) from employees;

2、将所有的人都統計進來

select avg(nvl(commission_pct,0)) from employees;

分組資料：GROUP BY

可以使用GROUP BY 子句将表中的資料分成若幹組.

group by 後面不能使用列别名，select 後面有限制.

1、求出EMPLOYEES中各個部門的平均工資

select department_id,avg(salary) from employees group by department_id order by department_id;

2、包含在 GROUP BY 子句中的列不必包含在SELECT 清單中。

select sum(salary) from employees group by job_id;

3、進行多組分列，按照部門和工作進行分組得到分組後工資的和

select department_id,job_id,sum(salary) from employees group by department_id,job_id order by department_id;

非法使用組函數

SELECT 清單中的列或表達式，未包含在組函數中的列，都必須包含于GROUP BY 子句中

錯誤：

select department_id, count(last_name) from employees;

或

select department_id, job_id, count(last_name) from employees group by department_id;

也就是說必須把department_id 和job_id 加入到group by 中

正确：

select department_id, count(last_name) from employees group by department_id;

select department_id, job_id, count(last_name) from employees group by department_id，job_id;

不能使用 WHERE 子句來過濾組

可以使用 HAVING 子句來過濾組

select department_id, avg(salary) from employees where avg(salary) > 8000 group by department_id;

過濾分組：HAVING 子句

使用 HAVING 子句過濾分組條件：

行已經被分組。

使用了組函數。

滿足HAVING 子句中條件的分組将被顯示

文法：

select column, group_function from table [where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

1、每個部門的最高薪水大于$10,000

select department_id,max(salary) from employees group by department_id having max(salary)>10000;

2.查找不是REP工作的工資總和大于13000的，并按照sum salary排序。

select job_id ,sum(salary) from employees where job_id not like '%REP%' group by job_id having sum(salary) > 13000

order by sum(salary);

嵌套組函數

按照部門分類顯示平均工資的最大值：

select max(avg(salary)) from employees group by department_id;

但是嵌套組函數好像不能在添加新的列了

練習題：

1、找出所有員工工資的最大值，最小值和以及平均值。并以此将各列的别名修改

為”Maximum”,”Minimum”,”Sum”,”Average”。并且要求将結果進行四舍五入。

select round（max(salary) ，0） "Maxinmum", round(min(salary),0) Minimum, round(sum(salary),0) Sum ,round(avg(salary),0) Average from employees;

2、以 job_id 進行分組，檢視每個工種的工資的最大值，最小值，和，以及平均值

select job_id,max(salary),min(salary),sum(salary),avg(salary) from employees group by job_id;

3、寫一個查詢語句，統計每一個工種的員工數

select job_id,count(employee_id）from employees group by job_id;

4、讓HR部門的同僚可以輸入一個工種，然後 SQL 傳回該工種的員工數量。

select job_id,count(*) from employees where job_id like '&job_title' group by job_id;

5、直接顯示出所有經理的總人數。并将該列标記為"Number of Managers".提示：使用 MANAGER_ID 這一列來确定經理的數量

select count(distinct manager_id) "Number of Managers" from employees;

6、查出薪水最高的和薪水最低的內插補點，并将該列标記為 DIFFERENCE

select max(salary) - min(salary) "DIFFERENCE" from employees;

7、請查詢出每個經理手下工資最低的員工，那些沒有經理的員工需要排除，并且需要排除那些最低薪水

小于等于6000 組。最後将結構根據薪水以降序排列。

select manager_id,min(salary)

from employees

where manager_id is not null

group by manager_id

having min(salary) > 6000

order by min(salary) desc;

8、請編寫一條 SQL 語句，檢視員工的總數，以及在 1996,1997,1998,1999 這幾年被雇傭的員工數量，并為各列取合适的别名。

select count(*) total,

sum(decode(to_char(hire_date, 'fm YYYY'),1999,1,0)) "1999",

sum(decode(to_char(hire_date, 'fm YYYY'),1998,1,0)) "1998",

sum(decode(to_char(hire_date, 'fm YYYY'),1997,1,0)) "1997",

sum(decode(to_char(hire_date, 'fm YYYY'),1996,1,0)) "1996"

from employees;

9、請通過一個矩陣顯示出所需要的結果，要求是根據部門編号（20,50,80,90）算出對應的工種的工

資，以及該工種的工資總和，對于部門号是 20,50,80,90 這幾列來說，請給出一個合适的别名。

select job_id "job",

sum(decode(department_id,20,salary)) "dept 20",

sum(decode(department_id,50,salary)) "dept 50",

sum(decode(department_id,80,salary)) "dept 80",

sum(decode(department_id,90,salary)) "dept 90",

sum(salary) "total"

group by job_id;

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1904417，如需轉載請自行聯系原作者

SQL 基礎之組函數（九）

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離