oracle 分组查询sql语句,oracle中SQL语句学习-（统计分组语句group by和having）

曾经终败给现在 2023-01-18 11:20 1阅读 0赞

注意：

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)

\---------------------

二、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代码  ![98c2219b996d31ec2d548a0ced1abb40.png][]

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)

\----------

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表达式可以多个条件进行判断，从而返回结果。

示例如下：

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

[98c2219b996d31ec2d548a0ced1abb40.png]: /images/20221021/b42ae13eff1e4049a470ce7245405af5.png