oracle分組函數

標籤：oracle   having   分組函數   

分組函數：作用於一組值，並對一組資料返回一個值；

分組函數的使用：

常用的分組函數：

Avg（求平均值）,sum（求總和）,min（求最小值）,max（求最大值）,count（求總個數）,wm_concat(行轉列)

例：select count( distinct deptno) from emp ;

行轉列： select deptno 部門號,wm_concat(ename) 部門中員工的名字  from emp group by deptno;

----------------------------------------------------

分組函數與空值：(分組函數會自動忽略空值)

1.      統計員工的平均工資

Select sum(sal)/count(*) 一，sum(sal)/count(sal) 二，avg(sal) 三 from emp;

 

2.      統計員工的平均獎金

Select sum(comm)/count(*)一,  sum(comm)/count(comm) 二，avg(comm) 三from emp;

 

注意：nvl函數使分組函數無法忽略空值

Selectsum(comm)/count(*)一,   sum(comm)/count(nvl(comm,0)) 二from emp;

Comm為空白時，nvl函數返回0,0不為null,所以count不會忽略

------------------------------------------------------

Group by子句的使用

1.求部門號和部門的平均工資

Selectdeptno,avg(sal) from emp

Group bydeptno;

 抽象：

Selecta,b,c,avg(x) from table

Group bya,b,c;

規定：在select列表中所有未包含在組函數中的列都應該包含在group by 子句中；

相反的，在group by 中的列，不必包含在組函數中

 

多個列的分組：(分組邏輯：先按照group by 後面第一個列分組，如果第一個列相同，再按照第二個列分組，如果所有的列都相同，那麼這就是同一組中的資料)

按照部門，不同的職位，統計員工工資的總額

Select deptno,job ,sum(sal) from emp

Group by deptno,job;

-------------------------------------------------------

Having子句的使用：（過濾分組後的資料）

平均工資大於2000的部門

Select deptno,avg(sal)

From emp

Having  avg(sql)>2000;

 

Where和having的區別：

在where子句後面不能使用分組函數

 

Where和having可以通用的情況(沒有分組函數的情況下可以通用，但是從

Sql最佳化的角度上考慮，盡量使用where)：

查詢10號部門的平均工資

1.      Where

Select deptno,avg(sal) from emp wheredeptno=10 group by deptno

 

2.      Having

Select deptno,avg(sal) from empgroup by deptno having deptno=10

-----------------------------------------------------------------------------

在分組函數中使用orderby 排序(預設是asc升序)

1.Select deptno ,avg(sal)

From emp

Group by deptno

Order by avg(sal)

2.

Select deptno ,avg(sal)

From emp

Group by deptno

Order by 2—這裡的2代表select語句後面的第二列，這裡即avg(sal)

----------------------------------------------------------------------------

分組函數的嵌套

Select  max(avg(sal))

From emp

Group by deptno;

-----------------------------------------------------------------------

Group by 語句的增強：

(1)Selext deptno,job,sum(sal)

From emp

Group by deptno,job

 

(2)Select deptno,sum(sal)

From emp

Having deptno

 

(3)Select sum(sal) from emp

 等價於（1）+（2）+（3）

Select deptno,job,sum(sal) from emp group byrollup(deptno,job);

文法：

Group by rollup(a,b)

等價於:groupby a,b+group by  a+group by null

 

Break on deptno skip 2;--相同的部門號只顯示一次，不同的部門號之間

跳過兩行

--詳細的可以看看sql-plus的報表功能

oracle分組函數