組函數及分組統計，組函數分組統計

組函數及分組統計，組函數分組統計

分組函數

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 dwhere e.deptno = d.deptnogroup 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 dwhere e.deptno = d.deptnohaving avg(sal) > 2000group by e.deptno;

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

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

-- 顯示非銷售人員工作名稱以及從事同一工作僱員的月工資總和，並且要滿足從事同一工作的僱員的月工資合計大於$5000,輸出結果按月工資合計升序排列select e.job, sum(e.sal) sum_salfrom emp ewhere e.job <> 'SALESMAN'group by e.jobhaving sum(e.sal) > 5000order 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)    ) twhere t.deptno=d.deptno;