組函數及分組統計

分組函數

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 empGROUP 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的部門編號和平均工資

剛開始學習的人非常easy錯誤地寫成將工資大于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_salfrom dept d,(select deptno,avg(sal) avg_salfrom empgroup by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno) ) t
where t.deptno=d.deptno;

?