06分组统计查询

分组函数：

统计函数COUNT()/SUM()/MIN()/MAX()/AVG()

 例：

SLEECT COUNT(*) 人数, SUM(sal) 总薪资，MIN（sal）最低薪资，MAX（sal）最高薪资，AVG（sal）平均薪资 FROM emp;

COUNT()函数有三种表现形式：

COUNT(*)：可以返回表中所有的记录数

COUNT(字段)：统计不为空的所有数据

COUNT(DISTINCT 字段）：消除重复数据的结果

 

select语句中每一个限定都是有执行顺序的：下面的1、2、3、4、5符号代表执行顺序。

4 select (distinct) 列名【别名】，列名【别名】，，。。。

1 from 表名【别名】

2【Where 条件限定】；

3 GROUP BY

HAVING 字句（必须跟GROUP BY一起出现），where是在GROUP BY之前执行的，所以where是不能跟统计函数的；所以用HAVING在GROUP BY之后做条件筛选，直接使用统计函数；

5 ORDER BY 列名 ASC/DESC

--from先执行，再到where，到group by，到select，最后是order by

 

例：

SELECT job,count(*),min(sal),max(sal)

FROM emp

GROUP BY job;

 

GROUP BY约定条件：

1.如果查询不使用GROUP BY子句，那么SELECT字句只允许出现统计函数，其他任何字段不允许出现。

例SELECT empno,COUNT(*) FROM emp;这是错误的。因为这里没有group by子句，

 

2.如果查询使用了GROUP BY子句，那么SELECT子句只允许出现统计函数、分组字段，其他任何字段都不允许出现；

例SELECT empno,job,COUNT(*) FROM emp GROUP BY job;是错误的；

--这里的分组字段是job，所以select字句只允许出现统计函数和job；

 

3.如果查询使用了嵌套函数，那么SELECT子句只允许出现嵌套函数，不允许出现任何字段包括分组字段。

 

例：查询每个部门的编号、名称、位置、部门人数、平均工资

数据表：

-dept：编号、名称、位置

-emp：统计数据：部门人数、平均工资

确定已知关联字段：

-雇员与部门：emp.deptno=dept.deptno;

1.查询部门的编号、名称、位置、部门人数用雇员编号（count（））、工资（AVG（））

SELECT d.deptno,d.dname,d.loc,e.empno,e.sal

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno;

 

2.发现三个重复d.deptno,d.dname,d.loc

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal)  --只能出现分组字段d.deptno,d.dname,d.loc

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

GROUP BY d.deptno,d.dname,d.loc；

 

 HAVING 字句

例：查询出每个职位的名称、平均工资，但是要求职位的平均工资高于2000

如果用以下代码是错误的：

SELECT job,AVG(sal)

FROM emp

WHERE AVG(sal)>2000(主要是因为WHERE字句不能直接使用统计函数，因为统计函数是在分组函数执行后才出现的，这样的话按照数据库执行顺序会先执行WHERE后执行GROUP BY，但是此时WHERE还不能使用统计函数；HAVING可以直接使用统计函数；

GROUP BY job;

应改为：

SELECT job,AVG(sal)

FROM emp

 GROUP BY job

HAVING AVG(sal)>2000;

 

范例：

1.显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和；并且要求满足从事同一工作月工资的合计大于5000，显示的结果按照月工作的合计的升序排列；

 

SELECT job,SUM(sal)

FROM emp

WHERE  job<>'salesman'

GROUP BY job

HAVING SUM(sal)>5000

ORDER BY SUM(sal) ASC;

 

2.统计所有销售人员（领取佣金）和非销售人员（不领取佣金）的平均工资和人数；

-先统计领取佣金的

SELECT COUNT(*),AVG(sal)

FROM emp

WHERE comm IS NOT NULL;

 

-统计不领取佣金的

SELECT COUNT(*),AVG(sal)

FROM emp

WHERE comm IS NULL;

 

-最后用UNION连接起来

SELECT COUNT(*),AVG(sal)

FROM emp

WHERE comm IS NOT NULL

UNION

SELECT COUNT(*),AVG(sal)

FROM emp

WHERE comm IS NULL;