組函數及分組統計

分組函數

SQL中常用的分組函數

Count(): 計數

Max()：求最大值

Min()：求最小值

Avg()：求平均值

Sum()：求和

-- 統計emp表中的人數
select count(*) from emp; 

-- 統計獲得獎金的人數
select count(comm) from emp;

-- 求所有雇員的最低工資
select min(sal) from emp;

-- 求所有雇員的最高工資
select max(sal) from emp;

-- 求部門編號為20的雇員的平均工資和總工資
select avg(sal),sum(sal) from emp where deptno = 20;

分組統計查詢

語法格式

SELECT {DISTINCT}*|查詢列1 別名1，查詢列2 別名2……

FORM 表名稱1 別名1,表名稱2 別名2,……

{WHERE 條件表達式}

{GROUP BY 分組條件}

{ORDERBY 排序字段 ASC|DESC,排序字段 ASC|DESC,……}

-- 統計出每個部門的人數
select deptno,count(empno) from emp group by deptno;

-- 求出每個部門的平均工資
select deptno, avg(sal) from emp group by deptno;

統計每個部門的最高工資，以及獲得最高工資的雇員姓名

如果寫成

SELECT ename,max(sal)
 FROM emp
 GROUP BY deptno

Oracle會提示第 1 行出現錯誤:

ORA-00979: 不是 GROUP BY 表達式

以上代碼在執行過程中出現錯誤，是因為:

1. 如果程序中使用了分組函數，則在以下兩種情況下可以正常查詢結果:

程序中存在了GROUP BY,並指定了分組條件，這樣可以將分組條件一起查詢出來

如果不使用GROUP BY,則只能單獨地使用分組函數

2．使用分組函數時，查詢結果列不能出現分組函數和分組條件之外的字段

綜上所述，我們在進行分組統計查詢時有遵循這樣一條規律:

出現在字段列表中的字段，如果沒有出現在組函數中，就必定出現在GROUP BY 語句的後面

-- 統計出每個部門的最高工資，及最高工資的雇員姓名
select deptno, ename,sal from emp where sal in(select max(sal) from emp group by deptno);

-- 查詢出每個部門的部門名稱，及每個部門的雇員人數
select d.dname, count(e.empno)
from emp e, dept d
where e.deptno = d.deptno
group by d.dname

求出平均工資大於2000的部門編號和平均工資

初學者很容易錯誤地寫成將工資大於2000的條件寫在where的後面

SELECT deptno,avg(sal)  FROM emp  WHERE avg(sal)>2000   GROUP BYdeptno<span style="font-family:SimSun;"></span>

系統出現如下錯誤提示:

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

-- 求出平均工資大於2000的部門編號和平均工資
select e.deptno, avg(sal)
from emp e, dept d
where e.deptno = d.deptno
having avg(sal) > 2000
group by e.deptno;

規則：WHERE 只能對單條記錄限制（過濾），having是對分組進行過濾

分組函數只能在分組中使用，不能在WHERE語句之中出現，如果要指定分組條件，則只能通過第二種條件的指令：HAVING

-- 顯示非銷售人員工作名稱以及從事同一工作雇員的月工資總和，並且要滿足從事同一工作的雇員的月工資合計大於$5000,輸出結果按月工資合計升序排列
select e.job, sum(e.sal) sum_sal
from emp e
where e.job <> 'SALESMAN'
group by e.job
having sum(e.sal) > 5000
order by sum_sal;

分組的簡單原則：
只要一列上存在重復內容才有可能考慮到用分組查詢

注意：

分組函數可以嵌套使用，但是在組函數嵌套使用的時候不能再出現分組條件的列名

例：求平均工資最高的部門編號、部門名稱、部門平均工資

第一步：

select deptno, avg(sal) from emp group by deptno;

第二步：

select deptno, max(avg(sal)) from emp group by deptno;

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

第三步：去掉查找結果中的deptno列

select max(avg(sal)) from emp group by deptno;

逐步完成後：

select d.deptno, d.dname, t.avg_sal
  from dept d,
   (select deptno,avg(sal) avg_sal
      from emp
       group by deptno having avg(sal)=
          (select max(avg(sal)) from emp group by deptno) 
   ) t
where t.deptno=d.deptno;