ITPub博客

首页 > 数据库 > Oracle > oracle中SQL语句学习-(统计分组语句group by和having)

oracle中SQL语句学习-(统计分组语句group by和having)

Oracle 作者:地铁上小虫 时间:2016-04-24 15:53:22 0 删除 编辑
在应用系统开发中,进行需要统计数据库中的数据,当执行数据统计时,需要将表中的数据进行分组显示,在统计分组中是通过group by子句、分组函数、having子句共同实现的。其中group by子句用于指定要分组的列,而分组函数用户指定显示统计的结果,而having子句用户限制显示分组结果。 
   一、分组函数 
   分组函数用于统计表的数据,并作用于多行,但是返回一个结果,一般情况下,分组函数要与group by子句结合使用,Oracle数据库提供了大量的分组函数,常用的五个分组函数: 
Max:该函数用于取得列或表达式的最大值,适用于任何数据类型。  
  1. Min:该函数用于取得列或表达式的最小值,适用于任何数据类型。  
  2. Avg:该函数用于取得列或表达式的平均值,适用于数字类型。  
  3. Sum:该函数用于取得列或表达式的总和,  适用于数字类型。  
  4. Count:该函数用于取的行数总和。  
注意: 
1、当使用分组函数时,分组函数只能出现在选择列表、order by和having子句中,而不能出现在where、group by子句中。 
2、当使用分组函数时,除了函数count(*)外,其他分组函数都会忽略NULL行。 
3、当执行select语句时,如果选择列表同时包括列、表达式和分组函数,那么这些列、表达式必须出现在group by子句中。 
4、当使用分组函数时,在分组函数中可以指定all和distinct选项,其中all是默认选项,该选项表示统计所有行数据(包括重复行),distinc可以统计不同行数据。 
示例如下: 
1、取得某列最小值、最大值、平均值、总和和总计行数 
HR@ PROD1>select EMPLOYEE_ID,min(SALARY),max(MANAGER_ID),sum(DEPARTMENT_ID),count(PHONE_NUMBER) from employees  group by EMPLOYEE_ID; 
HR@ PROD1>select count(distinct SALARY) from employees;
COUNT(DISTINCTSALARY)
---------------------
                   58
二、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进行单列分组,如下: 
HR@ PROD1>select EMPLOYEE_ID,min(SALARY) min_sal,max(SALARY) max_sal from employees group by employee_id;
2、使用having子句限制分组显示结果,如下: 
HR@ PROD1>select EMPLOYEE_ID,count(SALARY) count from employees group by employee_id having count(salary)=1;
三、case表达式 
case格式如下: 
Oracle代码  收藏代码
  1. case when 条件 then 返回值1 when 条件2 then 返回值2 else 返回值3 end 
HR@ PROD1>select EMPLOYEE_ID,FIRST_NAME,case when EMPLOYEE_ID=198 then '2600' when EMPLOYEE_ID=199 then '2600' else '1000' end alias from employees;
四、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为。 
示例如下: 
SYS@ PROD1>select mod(101,4) from dual;
MOD(101,4)
----------
         1
SYS@ PROD1>select round(101234.5678,3) from dual;
ROUND(101234.5678,3)
--------------------
          101234.568
SYS@ PROD1>select round(101234.5678,-3) from dual;
ROUND(101234.5678,-3)
---------------------
               101000
SYS@ PROD1>select round(101234.5678,-3) from dual;
ROUND(101234.5678,-3)
---------------------
               101000
SYS@ PROD1>select trunc(101234.456,2) from dual;
TRUNC(101234.456,2)
-------------------
          101234.45
2、日期函数 
   (1)、round(d,[fmt])该函数用于返回日期时间的四舍五入结果,如果fmt指定年度,则7月1日为分割线;如果fmt指定月,则16日为分割线;如果fmt指定为天,则中午12:00为分割线。 
   (2)、trunc(d,[fmt])该函数用于截取日期时间数据,如果fmt指定年度,则结果为本年度的1月1日,如果fmt指定月,则结果为本月1日。 
示例如下: 
SYS@ PROD1>select sysdate from dual;
SYSDATE
---------
24-APR-16
SYS@ PROD1>select round(sysdate,'yyyy') from dual;
ROUND(SYS
---------
01-JAN-16
SYS@ PROD1>select round(sysdate,'mm') from dual;
ROUND(SYS
---------
01-MAY-16
SYS@ PROD1>select round(sysdate,'dd') from dual;
ROUND(SYS
---------
24-APR-16
SYS@ PROD1>select trunc(sysdate,'yyyy') from dual;
SYS@ PROD1>select trunc(sysdate,'mm') from dual;
SYS@ PROD1>select trunc(sysdate,'dd') from dual;
  3、转换函数 
    (1)、to_char(date,fmt)该函数用于将日期类型转换为字符串类型,其中fmt用于指定日期格式。 
    (2)、to_date(char,fmt)该函数用于将符合特定日期格式的字符串转变为date类型的值。 
    (3)、to_number(char)该函数用于将符合特定数字格式的字符串转换为数字类型。 
示例如下: 
SYS@ PROD1>select to_date('2016-04-24','yyyy-mm-dd') from dual;
TO_DATE('
---------
24-APR-16
SYS@ PROD1>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016-04-24 11:13:16
SYS@ PROD1>select to_number('10.1234') from dual;
TO_NUMBER('10.1234')
--------------------
             10.1234
4、其他单行函数 
    (1)、decode(expr,search1,result1[,search2,result2,...],default)该函数用于返回匹配于特定表达式结果,如果search1匹配与expr,则返回结果result1,如果search2匹配expr,则返回结果result2,以此类推,如果没有任何匹配关系,则返回默认default。 
示例如下: 
HR@ PROD1>select first_name,decode(employee_id,'198','2600','199','2600','200','4400') as decodee from employees;
注意:decode函数和case表达式的用法基本相似,但是case表达式可以多个条件进行判断,从而返回结果。 
示例如下: 
  1. select name,case when (  
  2.              (age='bb21' and address='cc21')   
  3.              or (age='bb22' and address='cc22')   
  4.              or (age='bb23' and address='cc23')   
  5.            ) then 1 else 0 end as cases from cip_temps

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29653765/viewspace-2087321/,如需转载,请注明出处,否则将追究法律责任。

下一篇: day2
请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    49
  • 访问量
    155488