Oracle中SQL語句學習五（統計分組語句group by和having）

?

?

oracle（41）

在 應用系統開發中，進行需要統計數據庫中的數據，當執行數據統計時，需要將表中的數據進行分組顯示，在統計分組中是通過group by子句、分組函數、having子句共同實現的。其中group by子句用于指定要分組的列，而分組函數用戶指定顯示統計的結果，而having子句用戶限制顯示分組結果。?
???一、分組函數?
?? 分組函數用于統計表的數據，并作用于多行，但是返回一個結果，一般情況下，分組函數要與group by子句結合使用，Oracle數據庫提供了大量的分組函數，常用的五個分組函數：?

 Max：該函數用于取得列或表達式的最大值，適用于任何數據類型。??
 Min：該函數用于取得列或表達式的最小值，適用于任何數據類型。??
 Avg：該函數用于取得列或表達式的平均值，適用于數字類型。??
 Sum：該函數用于取得列或表達式的總和，??適用于數字類型。??
 Count：該函數用于取的行數總和。??

注意：?
1、當使用分組函數時,分組函數只能出現在選擇列表、order by和having子句中，而不能出現在where、group by子句中。?
2、當使用分組函數時，除了函數count(*)外，其他分組函數都會忽略NULL行。?
3、當執行select語句時,如果選擇列表同時包括列、表達式和分組函數,那么這些列、表達式必須出現在group by子句中。?
4、當使用分組函數時，在分組函數中可以指定all和distinct選項，其中all是默認選項，該選項表示統計所有行數據(包括重復行),distinc可以統計不同行數據。?

示例如下：?
1、取得某列最小值、最大值、平均值、總和和總計行數?

 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;??

二、group by和having子句?
?? group by子句是對統計的結果進行分組統計，而having子句用于限制分組顯示結果，語法如下：?
select column，group_function from table [where condition][group by group_by_experssion][having group_function];如上所示，column用于指定列表中的列或表達式，group_function用于指定分組函數，condition用于指定條件子句，group_by_experssion用于指定分組表達式，group_function用于指定排除分組結果條件。?
1、使用group by進行單列分組，如下：?

 select?id?as?id,min(age)?max_age,max(age)?max_age?from?cip_temps?group?by?id;??

2、使用having子句限制分組顯示結果，如下：?

 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??

示例如下：?
select name,age,address,case when id=21 then 'abc' when id=22 then 'def' else 'hij' end alias from cip_temps;?
四、Oracle常用統計函數?
1、數字函數?
? (1)、mod(m,n)該函數用于返回取得兩個數字相除后的余數，如果數字為0，則返回結果為m。?
? (2)、round(n,[m]該函數用于取得四舍五入運算，如果省略m，則四舍五入至整數位；如果m是負數，則四舍五入到小數點前m位；如果m是正數，則四舍五入到小數點后m位。?
? (3)、trunc(n,[m])該函數用于截取數字，如果省略m，則將數字n的小數部門截取；如果m為正數，則將數字n截取至小數點后的第m位，如果m為負數，則將數字n截取小數點的前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;??

2、日期函數?
?? (1)、round(d,[fmt])該函數用于返回日期時間的四舍五入結果，如果fmt指定年度，則7月1日為分割線；如果fmt指定月，則16日為分割線；如果fmt指定為天，則中午12:00為分割線。?
?? (2)、trunc(d,[fmt])該函數用于截取日期時間數據，如果fmt指定年度，則結果為本年度的1月1日，如果fmt指定月，則結果為本月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;??

? 3、轉換函數?
??? (1)、to_char(date,fmt)該函數用于將日期類型轉換為字符串類型，其中fmt用于指定日期格式。?
??? (2)、to_date(char,fmt)該函數用于將符合特定日期格式的字符串轉變為date類型的值。?
??? (3)、to_number(char)該函數用于將符合特定數字格式的字符串轉換為數字類型。?
示例如下：?

 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、其他單行函數?
??? (1)、decode(expr,search1,result1[,search2,result2,...],default)該函數用于返回匹配于 特定表達式結果，如果search1匹配與expr，則返回結果result1，如果search2匹配expr，則返回結果result2，以此類推， 如果沒有任何匹配關系，則返回默認default。?
示例如下：?

 select?name,decode(age,'bb21',id*10,'bb22',id*20,1000)?as?decodee?from?cip_temps;??

注意：decode函數和case表達式的用法基本相似，但是case表達式可以多個條件進行判斷，從而返回結果。?
示例如下：?

Oracle代碼??

 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