※ 下面内容整理时,参考了华夏名网的下面一篇文章。在此表示感谢!
本篇整理内容包括
■组(多行)函数
■用GROUP BY给数据分组
■用HAVING子句限制分组数据
■嵌套函数
SQL中的组函数
组函数也叫集合函数,返回基于多个行的单一结果,
行的准确数量无法确定,除非查询被执行并且所有的结果都被包含在内。
和单行函数不同的是,在解析时所有的行都是已知的。
由于这种差别使组函数和单行函数有在需求和行为上有微小的差异.
■组(多行)函数和单行函数相比,oracle提供了丰富的基于组的,多行的函数。
这些函数能在select或select的having子句中使用,当用于select子串时常常都和GROUP BY一起使用。
1、AVG([{DISYINCT|ALL}])
返回数值的平均值。缺省设置为ALL.
SQL>
SQL> SELECT AVG(sal),AVG(ALL sal),AVG(DISTINCT sal) FROM scott.emp;
AVG(SAL) AVG(ALLSAL) AVG(DISTINCTSAL)
---------- ----------- ----------------
2132.69230 2132.692307 2134.09090909091
SQL>
2、COUNT({*|DISTINCT|ALL} )
返回查询中行的数目,缺省设置是ALL,*表示返回所有的行。
3、MAX([{DISTINCT|ALL}])
返回选择列表项目的最大值,
如果x是字符串数据类型,他返回一个VARCHAR2数据类型,
如果X是个DATA数据类型,返回一个日期,
如果X是numeric数据类型,返回一个数字。
注意distinct和all不起作用,应为最大值和这两种设置是相同的。
4、MIN([{DISTINCT|ALL}])
返回选择列表项目的最小值。
5、STDDEV([{DISTINCT|ALL}])
返回选者的列表项目的标准差,所谓标准差是方差的平方根。
6、SUM([{DISTINCT|ALL}])
返回选择列表项目的数值的总和。
7、VARIANCE([{DISTINCT|ALL}])
返回选择列表项目的统计方差。
■用GROUP BY给数据分组
组函数用来操作那些已分好组的数据,我们告诉数据库用GROUP BY怎样给数据分组或分类,
当我们在SELECT语句的SELECT子句中使用组函数时,我们必须把为分组或非 常数 列放置在GROUP BY子句中,
如果没有用group by进行专门处理,那么缺省的分类是将整个结果设为一类。
(1)我们用deptno字段分类
SQL>
SQL> select t.deptno, count(*) as cnt
2 from emp t
3 group by t.deptno
4 /
DEPTNO CNT
------ ----------
1
30 6
20 5
10 2
SQL>
(2)如果我们要将结果按照zip_codes排序,能用ORDER BY语句,ORDER BY子句能使用列或组函数。
SQL>
SQL> select t.deptno, count(*) as cnt
2 from emp t
3 group by t.deptno
4 ORDER BY COUNT(*) DESC;
DEPTNO CNT
------ ----------
30 6
20 5
10 2
1
SQL>
■用HAVING子句限制分组数据
当我们需要指示数据库对行分组,然后限制分组后的行的输出时,正确的方法是使用HAVING语句:
(1)不使用having子句进一步筛选的情况时:
SQL>
SQL> select t.deptno,sum(t.sal) as sum_sal
2 from emp t
3 group by t.deptno
4 /
DEPTNO SUM_SAL
------ ----------
30 9400
20 10875
10 7450
SQL>
(2)使用having子句后
SQL>
SQL> select t.deptno,sum(t.sal) as sum_sal
2 from emp t
3 group by t.deptno
4 having sum(t.sal) > 7451
5 /
DEPTNO SUM_SAL
------ ----------
30 9400
20 10875
SQL>
■嵌套函数
函数能够嵌套。一个函数的输出能是另一个函数的输入。各嵌套函数有有一个可继承的执行过程。
函数的优先权只是基于位置的,函数遵循由内到外,由左到右的原则。
嵌套技术一般用于象DECODE这样的能被用于逻辑判断语句IF.THENELSE的函数。
嵌套函数能包括在组函数中嵌入单行函数,或在单行函数或组函数中嵌入组函数。
SQL>
SQL> SELECT deptno,
2 GREATEST(COUNT(DISTINCT job),COUNT(DISTINCT mgr)) cnt,
3 COUNT(DISTINCT job) jobs,
4 COUNT(DISTINCT mgr) mgrs
5 FROM emp
6 GROUP BY deptno;
DEPTNO CNT JOBS MGRS
------ ---------- ---------- ----------
10 2 2 1
20 4 3 4
30 4 4 2
1 1 0
SQL>