|
oracle 11g 007 sql 第5章 Group Functions and groub by and having 笔记
5.1 what are group Functions ------什么叫做组函数
常见的组函数分为：AVG(平均值)、count（计数函数）、max（最大值）、min（最小值）还有
Stddev（标准方差）、SUM（求和值）。
组函数的语法：
SELECT group_function(column), ...
FROM table
[WHERE condition]
[ORDER BY column];
5.2组函数是输入多个，输出一个的。
5.2.1 组函数不包括Null值，组函数是对多行进行操作的。
5.2.2组函数的例子：求头衔为'%REP%'的平均值、最大值、最小值、总值。
SQL> SELECT AVG(salary), MAX(salary),
2 MIN(salary), SUM(salary)
3 FROM employees
4 WHERE job_id LIKE '%REP%';
AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)
----------- ----------- ----------- -----------
8272.727272 11500 6000 273000
5.2.3组函数还可以用在date里，例子如下：
SQL> select min(hire_date),max(hire_date)
2 from employees;
MIN(HIRE_DATE) MAX(HIRE_DATE)
-------------- --------------
1987/6/17 2000/4/21
5.2.4使用组函数count的例子：count是统计有多少数据。
COUNT(*) returns the number of rows in a table:
SQL> select count(*) from
2 employees where department_id=50;
COUNT(*)
----------
45
COUNT(expr) returns the number of rows with non-null values for expr:
求出非空的值有多少行
SQL> select count(commission_pct)
2 from employees
3 where department_id=80;
COUNT(COMMISSION_PCT)
---------------------
34
5.3 在count里使用distinct 关键字：
5.3.1.using the distinct keyword
5.3.2.COUNT(DISTINCT expr) returns the number of distinct non-null values of expr.
在count里使用distinct求出非空值的数。
5.3.3.To display the number of distinct department values in the EMPLOYEES table
在employees表中求出distinct的count(department_id)有几行记录。
SQL> select count (distinct department_id)
2 from employees;
COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
11
5.4 Group Functions and Null Values
组函数会忽略哪些包含的Null值。
Group functions ignore null values in the column例子：
SQL> select avg(commission_pct)
2 from employees;
AVG(COMMISSION_PCT)
-------------------
0.222857142857143
Null值必须在组函数列里面。
The NVL function forces group functions to include null values例子:
SQL> select avg(nvl(commission_pct,0))
2 from employees;
AVG(NVL(COMMISSION_PCT,0))
--------------------------
0.0728971962616822
5.5 Grouping rows:GROUP BY clause、HAVING clause 用法：
5.5.1 GROUP BY clause：
group by与语法如下：
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
You can divide rows in a table into smaller groups by using the GROUP BY clause.
使用group by子句，你能在这一张表划分较小的的组（行）。
5.5.2 Using the GROUP BY Clause
使用 group by 的例子：求雇员表中的平均工资且按照部门ID划分。
SQL> select department_id,avg(salary)
2 from employees
3 group by department_id;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8600
30 4150
7000
20 9500
70 10000
90 19333.33333
110 10150
50 3475.555555
40 6500
80 8955.882352
10 4400
60 5760
12 rows selected
5.5.3 Using the GROUP BY Clause on Multiple Columns
使用 group by 按照多行进行划分。例子如下：
SQL> select department_id,job_id,sum(salary)
2 from employees
3 group by department_id,job_id
4 order by department_id;
DEPARTMENT_ID JOB_ID SUM(SALARY)
------------- ---------- -----------
10 AD_ASST 4400
20 MK_MAN 13000
20 MK_REP 6000
30 PU_CLERK 13900
30 PU_MAN 11000
40 HR_REP 6500
50 SH_CLERK 64300
50 ST_CLERK 55700
50 ST_MAN 36400
60 IT_PROG 28800
70 PR_REP 10000
80 SA_MAN 61000
80 SA_REP 243500
90 AD_PRES 24000
90 AD_VP 34000
100 FI_ACCOUNT 39600
100 FI_MGR 12000
110 AC_ACCOUNT 8300
110 AC_MGR 12000
SA_REP 7000
20 rows selected
5.6 Restricting Group Results with the HAVING Clause
having的语法如下：
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];
having语句的用法例子如下：
SQL> select department_id,max(salary)
2 from employees
3 group by department_id
4 having max(salary)>10000;
DEPARTMENT_ID MAX(SALARY)
------------- -----------
100 12000
30 11000
20 13000
90 24000
110 12000
80 14000
6 rows selected
以上就是having的用法，注意的是大家可能会想干嘛不用where子句，这里如果使用where子句会报错，因为where子句不能使用组函数。
5.7 Nesting group functions
嵌套组函数例子如下：
SQL> select max(avg(salary))
2 from employees
3 group by department_id;
MAX(AVG(SALARY))
----------------
19333.3333333333
| 
免费注册
发表于 2011-12-20 09:48