--=================================
--SQL 基础 --> 分组与分组函数
--=================================
/*
一、分组：
分组函数可以对行集进行操作，并且为每组给出一个结果。
使用 group by column1 ， column2,.. 按 columm1,column2 进行分组，即 column1,column2 组合相同的值为一个组
二、常用分组函数： */
AVG ( [DISTINCT|ALL]n ) -- 求平均值，忽略空值
COUNT ({*| [DISTINCT|ALL]expr} ) -- 统计个数，其中 expr 用来判定非空值 ( 使用 * 计算所有选定行，包括重复行和带有空值的行 )
MAX ( [DISTINCT|ALL]expr ) -- 求最大值，忽略空值
MIN ( [DISTINCT|ALL]expr ) -- 求最小值，忽略空值
SUM ( [DISTINCT|ALL]n ) -- 求和，忽略空值
/*
三、分组函数语法： */
SELECT [column,] group_function ( column ), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column] ;
/*
四、分组函数使用准则：
DISTINCT 使函数只考虑非重复值， ALL 则考虑包括重复值在内的所有值。默认为 ALL ．
带有 expr 参数的函数的数据类型可以为 CHAR,VARCHAR2,NUMBER,DATE.
所有分组函数都忽略空值。可以使用 NVL,NVL2, 或 COALESCE 函数代替空值
使用 GROUP BY 时， Oralce 服务器隐式地按照升序对结果集进行排序。可以使用 ORDER BY 更改排序结果。
可以使用 NVL 函数强制分组函数包含空值，如： */
select avg ( nvl ( comm , 0 )) from emp ;
/*
五、 GROUP BY 子句的语法：
使用 GROUP BY 子句可以将表中的行分成更小的组，然后使用分组函数返回每一组的汇总信息 */
SELECT column , group_function ( column )
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column] ;
GROUP BY --group_by_expression 即为对哪些列进行分组
/*
六、 GROUP BY 使用准则：
SELECT 中出现的列，如果未出现在分组函数中，则 GROUP BY 子句必须包含这些列
WHERE 子句可以某些行在分组之前排除在外
不能在 GROUP BY 中使用列别名
默认情况下 GROUP BY 列表中的列按升序排列
GROUP BY 的列可以不出现在分组中
七、分组过滤：
使用 having 子句
having 使用的情况：
行已经被分组
使用了组函数
满足 having 子句中条件的分组将被显示
八、演示： */
-- 为数字数据使用 AVG 和 SUM 方法
SQL > select min ( sal ) as min_sal , max ( sal ) as max_sal ,
2 avg ( sal ) as avg_sal , sum ( sal ) as sum_sal
3 from scott . emp ;
MIN_SAL MAX_SAL AVG_SAL SUM_SAL
---------- ---------- ---------- ----------
800 5000 2073.21429 29025
-- 对于数字 , 字符和日期数据类型 , 你能使用 MIN 和 MAX 方法
SQL > select min ( hiredate ) as min_hiredate , max ( hiredate ) as max_hiredate from scott . emp ;
MIN_HIRED MAX_HIRED
--------- ---------
17 - DEC - 80 23 - MAY - 87
-- 使用 count(*),count(expr),count(distinct expr)
-- 注意 coung(*) 包含空值、重复值， count(expr) 过滤空值， count(distinct expr) 即过滤空值，也过滤重复值
SQL > select count (*), count ( mgr ), count ( distinct mgr ) from emp ;
COUNT (*) COUNT ( MGR ) COUNT ( DISTINCTMGR )
---------- ---------- ------------------
14 13 6
-- 使用 NVL 函数强制分组函数包含空值
SQL > select avg ( comm ) , avg ( nvl ( comm , 0 )) from emp ;
AVG ( COMM ) AVG ( NVL ( COMM , 0 ))
---------- ----------------
550 157.142857
-- 使用 group by 子句来分组
SQL > select job , avg ( sal ) from emp group by job ;
JOB AVG ( SAL )
--------- ----------
CLERK 1037.5
SALESMAN 1400
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000
--GROUP BY 的列可以不出现在分组中
SQL > select avg ( sal ) from emp group by job order by avg ( sal ) desc ;
AVG ( SAL )
----------
5000
3000
2758.33333
1400
1037.5
-- 错误的用法， SELECT 中的有些列没有在 GROUP BY 子句中出现
SQL > select job , avg ( sal ) from emp ;
select job , avg ( sal ) from emp
*
ERROR at line 1 :
ORA - 00937 : not a single - group group function
-- 使用 having 子句过滤分组结果
-- 查询平均工资高于的部门号，及其平均工资。
select deptno , avg ( sal ) from emp group by deptno
having avg ( sal )> 2000 ;
-- 查出平均工资在以上的工种 (job)
select job , avg ( sal ) from emp group by job having avg ( sal )> 2000 ;
-- 求人数在人以上的部门
select deptno , count (*) from emp group by deptno having count (*)> 5 ;
-- 使用分组函数的嵌套
SQL > select max ( avg ( sal )) from emp group by deptno ;
MAX ( AVG ( SAL ))
-------------
2916.66667
/*
九、更多 */
使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 实例
Oracle 实例和 Oracle 数据库 (Oracle 体系结构 )
