在應用系統(tǒng)開發(fā)中，進行需要統(tǒng)計數(shù)據(jù)庫中的數(shù)據(jù)，當執(zhí)行數(shù)據(jù)統(tǒng)計時，需要將表中的數(shù)據(jù)進行分組顯示，在統(tǒng)計分組中是通過group by子句、分組函數(shù)、having子句共同實現(xiàn)的。其中g(shù)roup by子句用于指定要分組的列，而分組函數(shù)用戶指定顯示統(tǒng)計的結(jié)果，而having子句用戶限制顯示分組結(jié)果。
一、分組函數(shù)
分組函數(shù)用于統(tǒng)計表的數(shù)據(jù)，并作用于多行，但是返回一個結(jié)果，一般情況下，分組函數(shù)要與group by子句結(jié)合使用，Oracle數(shù)據(jù)庫提供了大量的分組函數(shù)，常用的五個分組函數(shù)：
- Max：該函數(shù)用于取得列或表達式的最大值，適用于任何數(shù)據(jù)類型。
- Min：該函數(shù)用于取得列或表達式的最小值，適用于任何數(shù)據(jù)類型。
- Avg：該函數(shù)用于取得列或表達式的平均值，適用于數(shù)字類型。
- Sum：該函數(shù)用于取得列或表達式的總和， 適用于數(shù)字類型。
- Count：該函數(shù)用于取的行數(shù)總和。
Max：該函數(shù)用于取得列或表達式的最大值，適用于任何數(shù)據(jù)類型。
Min：該函數(shù)用于取得列或表達式的最小值，適用于任何數(shù)據(jù)類型。
Avg：該函數(shù)用于取得列或表達式的平均值，適用于數(shù)字類型。
Sum：該函數(shù)用于取得列或表達式的總和， 適用于數(shù)字類型。
Count：該函數(shù)用于取的行數(shù)總和。
注意：
1、當使用分組函數(shù)時,分組函數(shù)只能出現(xiàn)在選擇列表、order by和having子句中，而不能出現(xiàn)在where、group by子句中。
2、當使用分組函數(shù)時，除了函數(shù)count(*)外，其他分組函數(shù)都會忽略NULL行。
3、當執(zhí)行select語句時,如果選擇列表同時包括列、表達式和分組函數(shù),那么這些列、表達式必須出現(xiàn)在group by子句中。
4、當使用分組函數(shù)時，在分組函數(shù)中可以指定all和distinct選項，其中all是默認選項，該選項表示統(tǒng)計所有行數(shù)據(jù)(包括重復行),distinc可以統(tǒng)計不同行數(shù)據(jù)。
示例如下：
1、取得某列最小值、最大值、平均值、總和和總計行數(shù)
- select max(id) as max_id,min(id) as min_id,avg(id) as avg_id,sum(id) as sum_id,count(*) as count from cip_temps;
select max(id) as max_id,min(id) as min_id,avg(id) as avg_id,sum(id) as sum_id,count(*) as count from cip_temps;
2、去除重復值
- select count(distinct id) from cip_temps;
select count(distinct id) from cip_temps;
二、group by和having子句
group by子句是對統(tǒng)計的結(jié)果進行分組統(tǒng)計，而having子句用于限制分組顯示結(jié)果，語法如下：
select column，group_function from table [where condition][group by group_by_experssion][having group_function];如上所示，column用于指定列表中的列或表達式，group_function用于指定分組函數(shù)，condition用于指定條件子句，group_by_experssion用于指定分組表達式，group_function用于指定排除分組結(jié)果條件。
1、使用group by進行單列分組，如下：
- select id as id,min(age) max_age,max(age) max_age from cip_temps group by id;
select id as id,min(age) max_age,max(age) max_age from cip_temps group by id;
2、使用having子句限制分組顯示結(jié)果，如下：
- select id as id,count(age) count from cip_temps group by id having count(age)=2;
select id as id,count(age) count from cip_temps group by id having count(age)=2;
三、case表達式
case格式如下：
- case when 條件 then 返回值1 when 條件2 then 返回值2 else 返回值3 end
case when 條件 then 返回值1 when 條件2 then 返回值2 else 返回值3 end
示例如下：
select name,age,address,case when id=21 then 'abc' when id=22 then 'def' else 'hij' end alias from cip_temps;
四、Oracle常用統(tǒng)計函數(shù)
1、數(shù)字函數(shù)
(1)、mod(m,n)該函數(shù)用于返回取得兩個數(shù)字相除后的余數(shù)，如果數(shù)字為0，則返回結(jié)果為m。
(2)、round(n,[m]該函數(shù)用于取得四舍五入運算，如果省略m，則四舍五入至整數(shù)位；如果m是負數(shù)，則四舍五入到小數(shù)點前m位；如果m是正數(shù)，則四舍五入到小數(shù)點后m位。
(3)、trunc(n,[m])該函數(shù)用于截取數(shù)字，如果省略m，則將數(shù)字n的小數(shù)部門截取；如果m為正數(shù)，則將數(shù)字n截取至小數(shù)點后的第m位，如果m為負數(shù)，則將數(shù)字n截取小數(shù)點的前m為。
示例如下：
- select mod(10,4) from dual;
- select round(101234.567,-4) from dual;
- select round(101.234567,4) from dual;
- select trunc(101234.457,2) from dual;
- select trunc(101234.457,-2) from dual;
select mod(10,4) from dual;
select round(101234.567,-4) from dual;
select round(101.234567,4) from dual;
select trunc(101234.457,2) from dual;
select trunc(101234.457,-2) from dual;
2、日期函數(shù)
(1)、round(d,[fmt])該函數(shù)用于返回日期時間的四舍五入結(jié)果，如果fmt指定年度，則7月1日為分割線；如果fmt指定月，則16日為分割線；如果fmt指定為天，則中午12:00為分割線。
(2)、trunc(d,[fmt])該函數(shù)用于截取日期時間數(shù)據(jù)，如果fmt指定年度，則結(jié)果為本年度的1月1日，如果fmt指定月，則結(jié)果為本月1日。
示例如下：
- select round(sysdate,'yyyy') from dual;
- select round(sysdate,'mm') from dual;
- select round(sysdate,'dd') from dual;
- select trunc(sysdate,'yyyy') from dual;
- select trunc(sysdate,'mm') from dual;
- select trunc(sysdate,'dd') from dual;
select round(sysdate,'yyyy') from dual;
select round(sysdate,'mm') from dual;
select round(sysdate,'dd') from dual;
select trunc(sysdate,'yyyy') from dual;
select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'dd') from dual;
3、轉(zhuǎn)換函數(shù)
(1)、to_char(date,fmt)該函數(shù)用于將日期類型轉(zhuǎn)換為字符串類型，其中fmt用于指定日期格式。
(2)、to_date(char,fmt)該函數(shù)用于將符合特定日期格式的字符串轉(zhuǎn)變?yōu)閐ate類型的值。
(3)、to_number(char)該函數(shù)用于將符合特定數(shù)字格式的字符串轉(zhuǎn)換為數(shù)字類型。
示例如下：
- select to_date('2009-3-1','yyyy-mm-dd') from dual;
- select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
- select to_number('10.123') from dual;
select to_date('2009-3-1','yyyy-mm-dd') from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
select to_number('10.123') from dual;
4、其他單行函數(shù)
(1)、decode(expr,search1,result1[,search2,result2,...],default)該函數(shù)用于返回匹配于特定表達式結(jié)果，如果search1匹配與expr，則返回結(jié)果result1，如果search2匹配expr，則返回結(jié)果result2，以此類推，如果沒有任何匹配關系，則返回默認default。
示例如下：
- select name,decode(age,'bb21',id*10,'bb22',id*20,1000) as decodee from cip_temps;
select name,decode(age,'bb21',id*10,'bb22',id*20,1000) as decodee from cip_temps;
注意：decode函數(shù)和case表達式的用法基本相似，但是case表達式可以多個條件進行判斷，從而返回結(jié)果。
示例如下：
- select name,case when (
- (age='bb21' and address='cc21')
- or (age='bb22' and address='cc22')
- or (age='bb23' and address='cc23')
- ) then 1 else 0 end as cases from cip_temps