SQL 基礎(chǔ)之組函數(shù)（九）

組函數(shù):

– 類型和語法

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

– 組函數(shù)使用 DISTINCT 關(guān)鍵字

– 組函數(shù)中NULL 值

分組函數(shù):作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個(gè)值

組函數(shù)類型

• AVG 平均值

• COUNT 統(tǒng)計(jì)值

• MAX 最大值

• MIN 最小值

• SUM 合計(jì)

• STDDEV 標(biāo)準(zhǔn)差

• VARIANCE 方差

組函數(shù)語法：

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

使用 AVG 和 和 SUM 函數(shù)

可以對數(shù)值型數(shù)據(jù)使用 AVG 和 SUM 函數(shù)

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

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

使用 MIN 和 和 MAX 

可以對數(shù)值型、字符型和日期型使用 MIN 和 MAX 函數(shù)

2、查詢?nèi)肼殨r(shí)最短和最長時(shí)間

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

使用 COUNT 

1、統(tǒng)計(jì)一下department_id 為50的部門有多少人

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

2、如果有空值不會(huì)被計(jì)算進(jìn)去

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

3、顯示 EMPLOYEES 表中不同的部門數(shù)

select count(distinct department_id) from employees;

組函數(shù)忽略空值

1、統(tǒng)計(jì)一下提成

select avg(commission_pct) from employees;

2、將所有的人都統(tǒng)計(jì)進(jìn)來

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

分組數(shù)據(jù)：GROUP BY

• 可以使用GROUP BY 子句將表中的數(shù)據(jù)分成若干組.

• group by 后面不能使用列別名，select 后面有限制.

1、求出EMPLOYEES中各個(gè)部門的平均工資

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、進(jìn)行多組分列，按照部門和工作進(jìn)行分組得到分組后工資的和

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

非法使用組函數(shù)

SELECT 列表中的列或表達(dá)式，未包含在組函數(shù)中的列，都必須包含于GROUP BY 子句中

錯(cuò)誤：

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 子句來過濾組

  
  

錯(cuò)誤：

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

過濾分組：HAVING  子句

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

• 行已經(jīng)被分組。

• 使用了組函數(shù)。

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

語法：

select column, group_function from table [where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

1、每個(gè)部門的最高薪水大于$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);

嵌套組函數(shù)

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

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

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

練習(xí)題：

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

為”Maximum”,”Minimum”,”Sum”,”Average”。并且要求將結(jié)果進(jìn)行四舍五入。

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 進(jìn)行分組，查看每個(gè)工種的工資的最大值，最小值，和，以及平均值

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

3、寫一個(gè)查詢語句，統(tǒng)計(jì)每一個(gè)工種的員工數(shù)

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

4、讓HR部門的同事可以輸入一個(gè)工種，然后 SQL 返回該工種的員工數(shù)量。

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

5、直接顯示出所有經(jīng)理的總?cè)藬?shù)。并將該列標(biāo)記為"Number of Managers".提示：使用 MANAGER_ID 這一列來確定經(jīng)理的數(shù)量

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

6、查出薪水最高的和薪水最低的差值，并將該列標(biāo)記為 DIFFERENCE

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

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

小于等于6000 組。最后將結(jié)構(gòu)根據(jù)薪水以降序排列。

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 語句，查看員工的總數(shù)，以及在 1996,1997,1998,1999 這幾年被雇傭的員工數(shù)量，并為各列取合適的別名。

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、請通過一個(gè)矩陣顯示出所需要的結(jié)果，要求是根據(jù)部門編號（20,50,80,90）算出對應(yīng)的工種的工

資，以及該工種的工資總和，對于部門號是 20,50,80,90 這幾列來說，請給出一個(gè)合適的別名。

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"

from employees

group by job_id;