Oracle系列：（12）多行函數

標籤：oracle

函數：oracle伺服器先事寫好的一段具有一定功能的程式片段，內建於oracle伺服器，供使用者調用 

單行函數：輸入一個參數，輸出一個結果，例如：upper(‘baidu.com‘)->BAIDU.COM

多行函數：輸入多個參數，或者是內部掃描多次，輸出一個結果，例如：count(*)->14

統計emp表中員工總人數

select count(*) from emp;

*號適用於表欄位較少的情況下，如果欄位較多，掃描時間多，效率低，項目中提倡使用某一個非null唯一的欄位，通常是主鍵 

統計公司有多少個不重複的部門

select count(distinct deptno) from emp;

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M00/86/F7/wKioL1fP5-jzPdcnAAAIxCfhpn4028.png" title="012.PNG" alt="wKioL1fP5-jzPdcnAAAIxCfhpn4028.png" />

統計有傭金的員工人數

select count(comm) from emp;

注意：今天講的這些多個行函數，不統計NULL值

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/86/F7/wKioL1fP6EvyJrbXAAAHHXCHrHE425.png" title="013.PNG" alt="wKioL1fP6EvyJrbXAAAHHXCHrHE425.png" />

員工總工資，平均工資，四捨五入，保留小數點後0位

select sum(sal) "總工資",round(avg(sal),0) "平均工資"from emp;

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M02/86/F8/wKiom1fP6IqSteiMAAANtFT0fec471.png" title="014.PNG" alt="wKiom1fP6IqSteiMAAANtFT0fec471.png" />

查詢員工表中最高工資，最低工資

select max(sal) "最高工資",min(sal) "最低工資"from emp;

入職最早，入職最晚員工

select max(hiredate) "最晚入職時間",min(hiredate) "最早入職時間"from emp;

多行函數：count/sum/avg/max/min

按部門求出該部門平均工資，且平均工資取整數，採用截斷

select deptno "部門編號",trunc(avg(sal),0) "部門平均工資"from empgroup by deptno;

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M00/86/F7/wKioL1fP6SzzdNLmAAATKAmEMqA240.png" title="015.PNG" alt="wKioL1fP6SzzdNLmAAATKAmEMqA240.png" />

(繼續)查詢部門平均工資大於2000元的部門

select deptno "部門編號",trunc(avg(sal),0) "部門平均工資"from empgroup by deptnohaving trunc(avg(sal),0) > 2000;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/86/F8/wKiom1fP6XSCDDQxAAAREdLPxhI958.png" title="016.PNG" alt="wKiom1fP6XSCDDQxAAAREdLPxhI958.png" />

(繼續)按部門平均工資降序排列

select deptno "部門編號",trunc(avg(sal),0) "部門平均工資"from empgroup by deptnohaving trunc(avg(sal),0) > 2000order by 2 desc;

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M01/86/F7/wKioL1fP6b-THbLUAAAV-mGM6ho064.png" title="017.PNG" alt="wKioL1fP6b-THbLUAAAV-mGM6ho064.png" />

除10號部門外，查詢部門平均工資大於2000元的部門，方式一【having deptno<>10】

select deptno,avg(sal)from empgroup by deptnohaving deptno<>10;

除10號部門外，查詢部門平均工資大於2000元的部門，方式二【where deptno<>10】【推薦】

select deptno,avg(sal)from empwhere deptno<>10group by deptno;

顯示部門平均工資的最大值

select max(avg(sal)) "部門平均工資的最大值"from empgroup by deptno;

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/86/F8/wKiom1fP6lDgOt1ZAAAPHLpPLeY622.png" title="018.PNG" alt="wKiom1fP6lDgOt1ZAAAPHLpPLeY622.png" />

思考：顯示部門平均工資的最大值和該部門編號?

select max(avg(sal)) "部門平均工資的最大值",deptno "部門編號"

from emp

group by deptno;

錯誤

group by 子句的細節： 1）在select子句中出現的非多行函數的所有列，【必須】出現在group by子句中 2）在group by子句中出現的所有列，【可出現、可不現】在select子句中 where和having的區別： where： 1）行過濾器 2）針對原始的記錄 3）跟在from後面 4）where可省 5）先執行 having： 1）組過濾器 2）針對分組後的記錄 3）跟在group by後面 4）having可省 5）後執行 oracle中綜合文法： 1）select子句-----必須 2）from子句-------必須，不知寫什麼表了，就寫dual 3）where子句------可選 4）group by子句---可選 5）having子句-----可選 6）order by 子句--可選，如果出現列名，別名，運算式，欄位

Oracle系列：（12）多行函數