转自CNOUG上oraclewindows的9i 007 个人总结
■■用组函数合计数据
组函数类型:
AVG 平均值
::COUNT 计数
::MAX 最大值
::MIN 最小值
::STDDEV 标准差
::SUM 合计
::VARIANCE 方差
函数 说明
AVG([DISTINCT|ALL]n) n 的平均值，忽略空值
COUNT({*|[DISTINCT|ALL]expr}) 行数，expr 求除了空计算(用 * 计数所有行，包括重复和带空值的行)
MAX([DISTINCT|ALL]expr) expr的最大值，忽略空值
MIN([DISTINCT|ALL]expr) expr的最小值，忽略空值
STDDEV([DISTINCT|ALL]x) n 的标准差，忽略空值
SUM([DISTINCT|ALL]n) 合计 n 的值，忽略空值
VARIANCE([DISTINCT|ALL]x) n 的方差，忽略空值
■组函数可以用在select 子句中
使用组函数的原则
::DISTINCT 使得函数只考虑不重复的值；ALL 使得函数考虑每个值，包括重复值。默认值是 ALL ，因此不需要指定。
::用于函数的参数的数据类型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。
::所有组函数忽略空值。为了用一个值代替空值，用 NVL、NVL2 或 COALESCE 函数。/./././././
::当使用 GROUP BY 子句时，Oracle 服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC 可以被用于 ORDER BY 子句。
强调用 DISTINCT 和组函数忽略空值。ALL 是默认。
eg:
SQL> create table test9
2 (id varchar2(10),
3 name varchar2(10))
4 tablespace users;
SQL> insert into test9 values('kjat','wps')
2 ;
已创建 1 行。
SQL> insert into test9 values(null ,null);
已创建 1 行。
SQL> insert into test9 values('kk',null);
已创建 1 行。
SQL> select distinct id
2 from test9;
ID
----------
kjat
kk
SQL> select count(*) from test9;
COUNT(*)
----------
3
SQL> select count(distinct *) from test9;
select count(distinct *) from test9
*
ERROR 位于第 1 行:
ORA-00936: 缺少表达式
SQL> select count(distinct id) from test9;
COUNT(DISTINCTID)
-----------------
2
SQL> select count(distinct name) from test9;
COUNT(DISTINCTNAME)
-------------------
1
SQL> select count(1) from test9;
COUNT(1)
----------
3
SQL> select count(2) from test9;
COUNT(2)
----------
3
SQL> select count(id) from test9;
COUNT(ID)
----------
2
SQL> select count(name) from test9;
COUNT(NAME)
-----------
1
SQL> insert into test9 values('kjat','ks'); //表中有两个kjat列
已创建 1 行。
SQL> select * from test9;
ID NAME
---------- ----------
kjat wps
//null
kk
kjat ks
SQL> select count(distinct id) from test9;
COUNT(DISTINCTID)
-----------------
2
SQL> select count(id) from test9;
COUNT(ID)
----------
3
注意:AVG、SUM、VARIANCE 和 STDDEV 函数只能被用于数字数据类型。
■COUNT 函数有三种格式：
::COUNT(*)
::COUNT(expr)
::COUNT(DISTINCT|Unique expr)
COUNT(*) 返回表中满足 SELECT 语句标准的行数，包括重复行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 语句中，COUNT(*) 返回满足 WHERE 子句条件的行数。
COUNT(expr) COUNT(expr) 返回对于表达式expr 非空值的行数
COUNT(DISTINCT expr) 返回对于表达式expr 非空并且值不相同的行数
所有组函数忽略列中的空值
eg:
select avg(commission_pct)from employees;
AVG(COMMISSION_PCT)
-------------------
.222857143
平均值只基于表中的那些 COMMISSION_PCT 列的值有效的行的计算。平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数 (4)。
select avg(nvl(commission_pct,0))
from employees;
AVG(NVL(COMMISSION_PCT,0))
--------------------------
.072897196
平均值被基于所有表中的行来计算，不管 COMMISSION_PCT 列是否为空。平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数 (20)。
■创建数据组:group by 子句语法
原则 /././././很重要
::如果在 SELECT 子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一个字段列表，你会收到一个错误信息。
::使用 WHERE 子句，你可以在划分行成组以前过滤行。
::在 GROUP BY 子句中必须包含列。
::在 GROUP BY 子句中你不能用列别名。
::默认情况下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆盖这个默认值。分组结果被以分组列隐式排序，可以用 ORDER BY 指定不同的排序顺序，但只能用组函数或分组列。
･在SELECT 列表中的不在组函数中的所有列必须在GROUP BY 子句中
即:当使用 GROUP BY 子句时，确保在 SELECT 列表中的所有没有包括在组函数中的列必须在 GROUP BY 子句中。
eg:
select department_id,avg(salary) //salary不用在group by 中,但department_id要在
from employees
group by department_id;
･GROUP BY 列不必在SELECT 列表中
eg:
select avg(salary) from employees
group by department_id;
･可以在 ORDER BY 子句中使用组函数。
eg:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary);
eg:
按department_id升序
SQL> select department_id,avg(salary)
2 from employees
3 group by department_id;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
10 4400
20 9500
30 4316.66667
40 6500
50 3475.55556
60 5760
70 10000
80 8955.88235
90 19333.3333
100 8600
110 10150
DEPARTMENT_ID AVG(SALARY)
------------- -----------
7000
已选择12行。
eg:
按avg(salary)升序
SQL> select department_id,avg(salary)
2 from employees
3 group by department_id
4 order by avg(salary);
DEPARTMENT_ID AVG(SALARY)
------------- -----------
50 3475.55556
30 4316.66667
10 4400
60 5760
40 6500
7000
100 8600
80 8955.88235
20 9500
70 10000
110 10150
DEPARTMENT_ID AVG(SALARY)
------------- -----------
90 19333.3333
已选择12行。
eg:
SELECT COUNT(*) total,
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1995,1,0))"1 995",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1 996",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1 997",
SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1 998"
FROM employees;
eg:
SELECT department_id dept_id,job_id,SUM(salary)
FROM employees
GROUP BY department_id,job_id;
･GROUP BY 子句指定你怎样分组行：
:: 首先，用部门号分组行。
:: 第二，在部门号的分组中再用 job ID 分组行。如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。
::在SELECT 列表中的任何列或表达式（非计算列）必须在GROUP BY 子句中
::在GROUP BY 子句中的列或表达式不必在SELECT 列表中
::在 SELECT 列表中的任何没有使用聚集函数的列或表达式必须放在 GROUP BY 子句中
eg:
SELECT department_id,COUNT(last_name)
FROM employees;
SELECT department_id, COUNT(last_name)
*
ERROR at line 1: //列未包含在group by子句中
ORA-00937: not a single-group group function
正确的是:
SELECT department_id, count(last_name)
FROM employees
GROUP BY department_id;
･非法使用group by 函数的查询
::不能使用WHERE 子句来约束分组
::可以使用HAVING 子句来约束分组
::在WHERE 子句中不能使用组函数作为条件，只能用非计算列
用 WHERE 子句约束选择的行，用 HAVING 子句约束组。
eg:
select department_id,avg(salary)
from employees
where avg(salary)>1000
group by department_id; //不能使用where子句约束分组.
正确的写法:
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>1000;
■having子句
当你使用 HAVING 子句时，Oracle 服务器执行下面的步骤：
1. 行被分组。
2. 组函数被用于分组。
3. 匹配 HAVING 子句的标准的组被显示。
･Oracle 服务器以下面的顺序求子句的值：
:: 如果语句包含一个 WHERE 子句，服务器建立候选行。
:: 服务器确定在 GROUP BY 子句中指定的分组。
:: HAVING 子句进一步约束结果那些在 HAVING 子句中不满足分组标准的组。
eg:
下面的例子显示那些最高薪水大于 $10,000 的部门的部门号和平均薪水。
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING max(salary)>10000;
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
显示那些合计薪水册超过 $13,000 的每个工作岗位的 job ID 和合计薪水。该例子排除了销售代表，并且用合计月薪排序列表
PU_CLERK|13900
AC_MGR|24000
AD_PRES|24000
IT_PROG|28800
AD_VP|34000
ST_MAN|36400
FI_ACCOUNT|39600
ST_CLERK|55700
SA_MAN|61000
SH_CLERK|64300
■组函数可以被嵌套两层深度
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;