Oracle_学习使用SQL语五(统计分组语句)

 在应用系统开发中，进行需要统计数据库中的数据，当执行数据统计时，需要将表中的数据进行分组显示，在统计分组中是通过group by子句、分组函数、having子句共同实现的。其中group by子句用于指定要分组的列，而分组函数用户指定显示统计的结果，而having子句用户限制显示分组结果。
   一、分组函数
   分组函数用于统计表的数据，并作用于多行，但是返回一个结果，一般情况下，分组函数要与group by子句结合使用，Oracle数据库提供了大量的分组函数，常用的五个分组函数：

Max：该函数用于取得列或表达式的最大值，适用于任何数据类型。
Min：该函数用于取得列或表达式的最小值，适用于任何数据类型。
Avg：该函数用于取得列或表达式的平均值，适用于数字类型。
Sum：该函数用于取得列或表达式的总和，  适用于数字类型。
Count：该函数用于取的行数总和。

   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;

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子句是对统计的结果进行分组统计，而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;

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;

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常用统计函数
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;

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;

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;

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;

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

http://shawnfree.iteye.com/blog/357375