SQL 分组函数介绍

**1、 **分组函数的概念 分组函数作用于一组数据，并对一组数据返回一个值。

2、 分组函数的类型： avg 平均值 count 统计值 max 最大值 min 最小值 sum 合计 stddev 标准差 variance 方差

a . AVG 和 SUM 函数 可以对数值型数据使用 AVG 和 SUM 函数

#查询公司中平均奖金是多少

SQL> select avg(sal) 2 from emp; AVG(SAL) ---------- 2073.21429

b . MIN 和 MAX 函数 可以对数值型、字符型和日期型数据使用 MIN 和 MAX 函数

#列出最早入职和最晚入职的时间，并计算出他们的天数差和月份差 增加列别名 days months

SQL> select min(hiredate),max(hiredate),max(hiredate)-min(hiredate) days,months_between(max(hiredate),min(hiredate)) months 2 from emp; MIN(HIREDA MAX(HIREDA DAYS MONTHS ---------- ---------- ---------- ---------- 1980-12-17 1987-05-23 2348 77.1935484

#查询各部门的最高薪水、最低薪水、平均薪水

SQL> select deptno,max(sal),min(sal),avg(sal) 2 from emp 3 group by deptno; DEPTNO MAX(SAL) MIN(SAL) AVG(SAL) ---------- ---------- ---------- ---------- 30 2850 950 1566.66667 20 3000 800 2175 10 5000 1300 2916.66667

c. count 函数 COUNT() 返回表中的行数： COUNT()返回满足SELECT语句条件的表中的行数，包括重复的行和任何列中包含空值的行。如果SELECT语句中包含WHERE子句，COUNT(*)返回满足WHERE子句中条件的行数。

# 返回表的行数 SQL> select count() from emp;

COUNT(*) ---------- 14

#查询公司中有奖金的人数

SQL> select count(*) 2 from emp 3 where comm!=0 or comm is not null; COUNT(*) ---------- 4

count（expr） 返回非控制的expr 的行数 返回由expr标识的列中非空值的数量。

#查询公司所有员工的个数

SQL> select count(ename) 2 from emp; COUNT(ENAME) ------------ 14

d . DISTINCT 关键字

count （distinct expr） 返回 expr 非空且不重复的记录数。

SQL> select count(distinct deptno) 2 from emp; COUNT(DISTINCTDEPTNO) --------------------- 3

e . 分组函数和空值

SQL> select comm from emp; COMM ---------- 300 500 1400 0 14 rows selected.

分组函数忽略空值：

SQL> select avg(comm) from emp; AVG(COMM) ---------- 550

NVL 函数使分组函数无法忽略空值：

SQL> select avg(nvl(comm,0)) from emp; AVG(NVL(COMM,0)) ---------------- 157.142857 分组数据 有时需要将信息表分成更小的组。这可以通过使用GROUP BY子句来实现。 可以使用GROUP BY 子句将表中的数据分成若干组。

#查看公司各职位的的员工个数，按照人数最多到最少排序

SQL> select job,count(*) 2 from emp 3 group by job 4 order by count(*) desc; JOB COUNT(*) --------- ---------- CLERK 4 SALESMAN 4 MANAGER 3 ANALYST 2 PRESIDENT 1

4、过滤分组 不能使用where 子句过滤组， 可以使用having 子句过滤组 HAVING 子句 使用HAVING子句过滤分组：

行已经被分组使用了分组函数满足having子句中条件的分组将被显示

#查询各部门的平均薪水及部门编号，要求只有员工姓名中包含‘A’才参与统计，只列出平均薪水>1500的，按照平均薪水升序排列

SQL> select deptno,avg(sal) 2 from emp 3 where ename like '%A%' 4 group by deptno 5 having avg(sal)>1500 6 order by avg(sal); DEPTNO AVG(SAL) ---------- ---------- 30 1580 10 2450

#按照部门分组统计，求最高薪水，平均薪水，最低薪水，只有薪水是1200以上的员工才参与统计，并且分组结果中只包含平均薪水在1500以上的部门，并且按照平均薪水倒序排列

SQL> select deptno,max(sal),min(sal),avg(sal) 2 from emp 3 where sal > 1200 4 group by deptno 5 having avg(sal)>1500 6 order by avg(sal) desc; DEPTNO MAX(SAL) MIN(SAL) AVG(SAL) ---------- ---------- ---------- ---------- 20 3000 2975 2991.66667 10 5000 1300 2916.66667 30 2850 1250 1690

#查询最高工资大于4000的部门编号

SQL> select deptno 2 from emp 3 group by deptno 4 having max(sal)>4000; DEPTNO ---------- 10