oracle sql中常用的多行函数

oracle 多行函数也称聚集函数，是对一组数据进行处理，返回一行。

求和函数sum

语法格式：SUM([DISTINCT|ALL] n)

distinct 表示重复的值只计算一次。默认使用的是all，n 表示sum函数作用的域（列）。

查询每个部门的总工资。

SQL> select department_name,sum(salary) "Total Salary"

2 from hr.employees join hr.departments using(department_id)

3 group by department_name;

DEPARTMENT_NAME Total Salary

------------------------------ ------------

Administration 4400

Accounting 20300

Purchasing 24900

Human Resources 6500

IT 28800

Public Relations 10000

Executive 58000

Shipping 156400

Sales 304500

Finance 51600

Marketing 19000

11 rows selected.

查询09年4月2号的销售总额。

SQL> select sum(quantity*paideach) "Total Sales"

2 from orderitems join orders using(order#)

3 where rderdate='02-APR-09';

Total Sales

-----------

375.75

平均值函数avg

语法格式：AVG([DISTINCT|ALL]n)

查询计算机类图书的平均利润。

SQL> select avg(retail-cost) "Average profit"

2 from books

3 where category='COMPUTER';

Average profit

--------------

18.2625

查询IT部门的平均工资。

SQL> select department_name,avg(salary) "Average Salary"

2 from hr.employees join hr.departments using(department_id)

3 where department_name='IT'

4 group by department_name;

DEPARTMENT_NAME Average Salary

------------------------------ --------------

IT 5760

count 函数

语法格式：COUNT(* [DISTINCT|ALL]c)

其中c 表示数值列，或者非数值列。

查看部门的数量。

SQL> select count(distinct department_id) from hr.employees;

COUNT(DISTINCTDEPARTMENT_ID)

----------------------------

11

查看下employees 中有几条记录，当你遇到一个陌生的表的时候，往往需要知道其中存放着

多少条记录。那就使用count(*)吧.

SQL> select count(*) from hr.employees;

COUNT(*)

----------

107

使用count(*) 才不会忽略null值。

SQL> select count(*) from orders;

COUNT(*)

----------

21

SQL> select count(shipdate) from orders;

COUNT(SHIPDATE)

---------------

15

因为有些订单还没有发货，所有shipdate 是null。

max 函数

语法格式：MAX([DISTINCT|ALL]c)

其中c 表示数值，时间，字符列。

查看下谁的工资最高。

SQL> select last_name,first_name,salary from hr.employees

2 where salary=( select max(salary) from hr.employees);

LAST_NAME FIRST_NAME SALARY

------------------------- -------------------- ----------

King Steven 24000

当max 函数作用于时间列的时候，返回的最大值是离当前时间最近的。

SQL> select max(hire_date) from hr.employees;

MAX(HIRE_

---------

21-APR-00

当max 函数作用于字符串列的时候，返回的是最接近Z或者z的字符串.z>Z。

SQL> select max(last_name||' '||first_name)

2 from hr.employees;

MAX(LAST_NAME||''||FIRST_NAME)

----------------------------------------------

Zlotkey Eleni

min 函数

语法格式：MIN([DISTINCT|ALL]c)

查询最早出版的书.

SQL> select title,pubdate from books

2 where pubdate=(select min(pubdate) from books);

TITLE PUBDATE

------------------------------ ---------

HOW TO MANAGE THE MANAGER 09-MAY-03

group by 子句

语法格式： GROUP BY columnname [, columnname,...],

其中的column 是用来创建分组的。

适用group by 子句的注意事项：

1、如果在select 子句中使用了聚集函数，任何在select 子句中没有使用聚集函数的列

都应该列在group by 子句中.

SQL> select department_name,sum(salary) "Total Salary"

2 from hr.employees join hr.departments using(department_id);

select department_name,sum(salary) "Total Salary"

*

ERROR at line 1:

ORA-00937: not a single-group group function

上面的语句看起来没有什么问题，本意是想查询每个部门总的工资的。

2、group by 子句可以使用没有列在select 子句中的列.

3、group by 子句中不能使用别名。

4、包含group by 的select 语句返回的查询默认是按照升序排列的，如果需要按照降序排列

可以使用order by 子句。

查询每个图书分类的利润。

SQL> select category,avg(retail-cost) "Profit"

2 from books

3 group by category;

CATEGORY Profit

------------ ----------

COMPUTER 18.2625

COOKING 8.6

CHILDREN 12.89

LITERATURE 18.1

BUSINESS 16.55

FITNESS 12.2

FAMILY LIFE 24.875

SELF HELP 12.1

8 rows selected.

having 子句

语法格式：group function comparison operator value.

注意：如果你需要对聚集函数中添加一些限制，应该使用having 子句，而不是where 子句。

where 子句中不能使用聚集函数。having 子句中也不能使用别名.

查询利润大于15美元的图书分类.

SQL> select category,to_char(avg(retail-cost),'$999.99') "Profit"

2 from books

3 group by category

4 having avg(retail-cost)>15;

CATEGORY Profit

------------ --------

COMPUTER $18.26

LITERATURE $18.10

BUSINESS $16.55

FAMILY LIFE $24.88

查询部门总工资小于5000美元的部门。

SQL> select department_name,sum(salary) "Total Salary"

2 from hr.employees join hr.departments using(department_id)

3 group by department_name

4 having sum(salary)<5000;

DEPARTMENT_NAME Total Salary

------------------------------ ------------

Administration 4400

在having 子句中支持比较运算符(>,

下面来看看这两天SQL语句。

SQL> select category,avg(retail-cost) "Average Profit"

2 from books

3 where category in ('CHILDREN','COMPUTER','BUSINESS')

4 group by category

5 having avg(retail-cost)>15;

CATEGORY Average Profit

------------ --------------

COMPUTER 18.2625

BUSINESS 16.55

SQL> select category,avg(retail-cost) "Avgrage Profit"

2 from books

3 group by category

4 having avg(retail-cost)>15

5 and category in ('CHILDREN','COMPUTER','BUSINESS');

CATEGORY Avgrage Profit

------------ --------------

COMPUTER 18.2625

BUSINESS 16.55

两者都是正确的，但是第一种的查询效率将会更高，因为使用了where 子句来过滤掉不需要

处理的分类。然后在进行聚集处理，过滤。而第二种方法做了很多额外的无用功。虽然这里

的效率是无所谓的，因为才那么几条记录需要处理，但是如果查询要处理的记录数很多，那

区别就出来了。

注：当一个select 语句中存在where,group by,having 子句的时候处理的顺序是：

1、先处理where 子句。

2、在处理group by 子句。

3、在处理having 子句。

本质上，group by 子句在where 子句过滤以后的临时记录集上操作。having 再去过滤group by

函数处理以后的临时记录集。

单行函数可以与多行函数相互嵌套，多行函数的嵌套深度是2.

SQL> select avg(sum(quantity*paideach)) "Average order profit"

2 from orderitems

3 group by order#;

Average order profit

--------------------

80.3833333

上面的输出似乎不太需要，我只需要保留两位小数点后两位就可以了。可以这样处理。

SQL> select to_char(avg(sum(quantity*paideach)),'$999.99') "Average order profit"

2 from orderitems

3 group by order#;

Average

--------

$80.38

标准差函数stddev

语法格式：STDDEV([DISTINCT|ALL]n)

其中n表示处理的数值列.

查询下各部门的贫富差距。

SQL> select department_id,count(*),to_char(avg(salary),'99999.99') "Average",

2 to_char(stddev(salary),'99999.99') "Sttddev"

3 from hr.employees

4 group by department_id;

DEPARTMENT_ID COUNT(*) Average Sttddev

-------------- -------------- --------- ---------

100 6 8600.00 1801.11

30 6 4150.00 3362.59

1 7000.00 .00

20 2 9500.00 4949.75

70 1 10000.00 .00

90 3 19333.33 4041.45

110 2 10150.00 2616.30

50 45 3475.56 1488.01

40 1 6500.00 .00

80 34 8955.88 2033.68

10 1 4400.00 .00

ITPUB个人空间!G6e&ywpu

DEPARTMENT_ID COUNT(*) Average Sttddev

-------------- -------------- --------- ---------

60 5 5760.00 1925.62

12 rows selected.

由查询可知道部门编号为20 的员工之间的工资差异最大。下面来看看这是什么部门。

SQL> select department_name from hr.departments

2 where department_id=20;

DEPARTMENT_NAME

------------------------------

Marketing

是市场营销部，难怪部门员工之间的工资水平差异那么大。

方差函数variance

语法格式：VARIANCE([DISTINCT|ALL]n)

由下面的查询可以看出，family life 类的图书的利润波动范围是最大的。

SQL> select category,to_char(variance(retail-cost),'999.99') "Variance",

2 min(retail-cost) "Min",max(retail-cost) "Max"

3 from books

4 group by category;

CATEGORY Varianc Min Max

------------ ------- -------------- --------------

COMPUTER 126.04 3.2 28.7

COOKING 2.65 7.45 9.75

CHILDREN 171.50 3.63 22.15

LITERATURE .00 18.1 18.1

BUSINESS .00 16.55 16.55

FITNESS .00 12.2 12.2

FAMILY LIFE 583.11 7.8 41.95

SELF HELP .00 12.1 12.1

8 rows selected.

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

转载于:http://blog.itpub.net/26842371/viewspace-720106/