分组函数:
单行函数和分组函数的区别:
单行:输入一行,输出也是一行。处理一行
分组:输入可能多行(一组),输出只有一行。处理多行
• MIN函数和MAX函数
–MIN和MAX函数主要是返回每组的最小值和最大值。
•MIN([DISTINCT|ALL] column|expression)
•MAX([DISTINCT|ALL] column|expression)
–MIN和MAX可以用于任何数据类型
–查询入职日期最早和最晚的日期
SQL> SELECT MIN(hiredate), MAX(hiredate)
2 FROM emp;
MIN(HIRED MAX(HIRED
--------- ---------
17-DEC-80 12-JAN-83
• SUM函数和AVG函数
–SUM和AVG函数分别返回每组的总和及平均值。
•SUM([DISTINCT|ALL] column|expression)
•AVG([DISTINCT|ALL] column|expression)
–SUM和AVG函数都是只能够对数值类型的列或表达式操作。
–查询职位以SALES开头的所有员工平均工资、最低工资、最
高工资、工资和。
SQL> SELECT AVG(sal), MAX(sal),
2 MIN(sal), SUM(sal)
3 FROM emp
4 WHERE job LIKE 'SALES%';
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)
-------- --------- --------- ---------
1400 1600 1250 5600
COUNT函数
–COUNT函数的主要功能是返回满足条件的每组记录条数。
•COUNT( *|{[DISTINCT|ALL] column|expression})
–COUNT(*):*要用括号括起来,返回表中满足条件的行记录数(不去除空记录,其它都是去除空记录进行统计的)
–查询部门30有多少个员工
SQL> SELECT COUNT(*)
2 FROM emp
3 WHERE deptno = 30;
COUNT(*)
---------
6
组函数中DISTINCT
–DISTINCT会消除重复记录后再使用组函数
–查询有员工的部门数量。
SQL> SELECT COUNT(DISTINCT deptno)
2 FROM emp;
分组函数中空值处理
–除了COUNT (*)之外,其它所有分组函数都会忽略列中的空
值,然后再进行计算。
SQL> SELECT AVG(comm)
2 FROM emp;
AVG(COMM)
---------
550
• 在分组函数中使用NVL函数
–NVL 函数可以使分组函数强制包含含有空值的记录
SQL> SELECT AVG(NVL(comm,0))
2 FROM emp;
AVG(NVL(COMM,0))
----------------
157.14286
练习1
• 1.查询部门20的员工,每个月的工资总和及平均 工资。
SELECT SUM (sal),AVG(sal)
FROM emp
GROUP BY deptno
HAVING deptno=20
• 2.查询工作在CHICAGO的员工人数,最高工资及最低工资。
SELECT COUNT(ename),MAX(sal),MIN(sal)
FROM emp,dept
WHERE emp.deptno=dept.deptno
AND dept.loc='CHICAGO'
• 3.查询员工表中一共有几种岗位类型。
SELECT COUNT (DISTINCT job)
FROM emp
数据组:
使用 GROUP BY 子句
• 在SELECT列表中除了分组函数那些项(比如sum,avg等函数),所有列(只要select后边出现,那么group by中也必须出现)都必须包含在GROUP BY 子句中。
SQL> SELECT deptno, AVG(sal)
2 FROM emp
3 GROUP BY deptno;
DEPTNO AVG(SAL)
--------- ---------
10 2916.6667
20 2175
30 1566.6667
• GROUP BY 所指定的列并不是必须出现在SELECT 列表中。
SQL> SELECT AVG(sal)
2 FROM emp
3 GROUP BY deptno;
AVG(SAL)
---------
2916.6667
2175
1566.6667
按多列分组的GROUP BY子句 :
• 查询每个部门每个岗位的工资总和。
SQL> SELECT deptno, job, sum(sal)
2 FROM emp
3 GROUP BY deptno, job;
DEPTNO JOB SUM(SAL)
--------- --------- ---------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
...
9 rows selected.
练习2
• 1.查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资。
SELECT emp.deptno,dname,COUNT(empno),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp,dept
WHERE emp.deptno=dept.deptno(+)
GROUP BY emp.deptno,dname
• 2.查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,最高工资,最低工资 ,工资总和,平均工资。
SELECT e.deptno,d.dname,e.job,COUNT(e.empno),MAX(e.sal),MIN(e.sal),SUM(e.sal),
AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname,e.job
• 3.查询每个经理所管理的人数,经理编号,经理
姓名,要求包括没有经理的人员信息。
SELECT COUNT(e.empno ),m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
GROUP BY m.empno,m.ename
排除组结果:
使用组函数的非法的查询
• 不能在WHERE子句中限制组
• 可以通过HAVING 子句限制组
SQL> SELECT deptno, max(sal)
2 FROM emp
3 WHERE max(sal) > 2900
4 GROUP BY deptno;
WHERE AVG(sal) > 2000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Select语句的书写顺序:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
SELECT语句执行过程
• SELECT语句执行过程:
–1.通过FROM子句中找到需要查询的表;
–2.通过WHERE子句进行非分组函数筛选判断;
–3.通过GROUP BY子句完成分组操作;
–4.通过HAVING子句完成组函数筛选判断;
–5.通过SELECT子句选择显示的列或表达式及组函数;
–6.通过ORDER BY子句进行排序操作。
练习3
• 1.查询部门人数大于2的部门编号,部门名称,部门人数。
SELECT e.deptno,dname,COUNT(empno)
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,dname
HAVING COUNT(empno)>2
• 2.查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,部平均工资 ,并按照部门人数升序排序。
SELECT e.deptno,d.dname,COUNT(empno),AVG(sal)
FROM emp e, dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname
HAVING COUNT(empno)>2 AND AVG(sal)>2000
ORDER BY COUNT(empno)ASC
组函数的嵌套
• 显示平均薪水的最大值
SQL> SELECT max(avg(sal))
2 FROM emp
3 GROUP BY deptno;
MAX(AVG(SAL))
-------------
2916.6667
• 注:与单行函数不同,组函数只能嵌套两层
课后作业
• 1.查询部门平均工资在2500元以上的部门名称及平均工资。
SELECT d.dname,AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname
HAVING AVG(sal)>2500
• 2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资,并按平均工资降序排序。
SELECT e.job,AVG(sal)
FROM emp e
WHERE e.job NOT LIKE 'SA%'
GROUP BY e.job
HAVING AVG(sal)>2500
ORDER BY AVG(sal) DESC
• 3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。
SELECT d.dname,round(MIN(sal),0),round(MAX(sal),0)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY e.deptno,d.dname
HAVING COUNT(empno)>2
• 4.查询岗位不为SALESMAN,工资和大于等于2500的岗位及每种岗位的工资和。
SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job
HAVING SUM(sal)>2500
• 5.显示经理号码和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序。
SELECT m.empno,m.ename,MIN(e.sal)
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
GROUP BY e.mgr,m.empno,m.ename
HAVING MIN(e.sal)>=3000
ORDER BY MIN(e.sal) DESC
• 6.写一个查询,显示每个部门最高工资和最低工资的差额。
SELECT MAX(sal)-MIN(sal)
FROM emp
GROUP BY deptno