SQL Fundamentals: 分组统计查询(FROM-WHERE-GROUPBY-HAVING-SELECT-ORDER BY)

SQL Fundamentals || Oracle SQL语言

统计函数

单字段分组统计（GROUP BY）

多字段分组统计

HAVING子句

 

• 控制操作的显示列：基本的SELECT语句
• 控制行：限定查询和排序显示
• 分组统计查询
  

 

各个子句的执行顺序：

1、FROM-->

2、WHERE -->

3、GROUP BY（HAVING子句）-->

4、SELECT-->

5、ORDER BY-->

 

 

一、统计函数/分组函数，

只有前5个是标准统计函数，其他的是扩展函数

五个核心的统计函数：COUNT()、AVG()、SUM()、MIN()、MAX()

只有COUNT()函数可以在表中没有任何记录时返回数字结果.

组函数	描述
COUNT(* | [DISTINCT] 列)	求出全部的记录数
SUM(列)	求出总和，操作的列是数字
AVG(列)	平均值
MAX(列)	最大值
MIN(列)	最小值
MEDIAN(列)	返回中间值（）
VARIANCE(列)	返回方差
STDDEV(列)	返回标准差

统计出公司的雇员人数

SELECT COUNT(empno) , COUNT(*) FROM emp ;

 

对于COUNT()函数而言，可以传递三种内容：

COUNT(*)
COUNT(字段)	如果列上存在null，那么null不会进行统计 适用于不可能为null的列进行统计.
COUNT(DISTINCT字段)	如果列上有重复，那么重复记录不统计

验证COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的使用区别

SELECT COUNT(*) , COUNT(ename) , COUNT(comm) , COUNT(DISTINCT job) FROM emp ;

验证三种COUNT()函数的使用方式（COUNT()永远有数据返回）

SELECT COUNT(ename) , AVG(sal) , SUM(sal) , MAX(sal) , MIN(sal) FROM bonus ;

 

• 例子：查询每个月工资总和
• SQL> SELECT SUM(sal) FROM emp;
• 例子：查询平均工资，最高低的工资
• SQL> SELECT AVG(sal),ROUND(AVG(sal),2),MAX(sal),MIN(sal) FROM emp;
• 例子：统计出公司最早雇佣和最晚雇佣的雇佣日期
• SELECT MIN(hiredate) , MAX(hiredate) FROM emp ;
• 例子：统计公司工资之中中间的工资值

SELECT MEDIAN(sal) FROM emp ;

• 例子：统计工资的标准差与方差

SELECT STDDEV(sal),VARIANCE(sal) FROM emp ;

 

二、单字段分组统计（GROUP BY 子句）

利用GROUP BY可以设置分组.

什么时候需要分组？

如果要进行某个字段的分组，那么这个字段列的内容一定会存在重复.

可以直接查看表中全部数据，以确定是否存在可以分组的字段.

分组统计语法：此时SELECT子句中只允许出现“分组字段”和“统计函数”

SELECT [DISTINCT] 分组字段 [AS] [列别名] ,... | 统计函数 [AS] [别名] , ….

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段]

[ORDER BY 排序字段 ASC|DESC] ;

例子：统计每个部门的人数

SELECT deptno,COUNT(*)

FROM emp

GROUP BY deptno ;

例子：统计每种职位的最高最低工资

SELECT job, MIN(sal),MAX(sal)

FROM emp

GROUP BY job;

 

SELECT子句在分组操作中的限制（红色字体为错误例子）

没有GROUP BY子句——SELECT子句（统计函数）	如果没有GROUP BY子句，则在SELECT子句之中只允许出现统计函数，其他任何字段都不允许出现。 SELECT deptno , COUNT(empno) FROM emp ; SELECT COUNT(empno) FROM emp ;
有GROUP BY子句——SELECT子句（分组字段，统计函数）	在统计查询之中，SELECT子句后只允许出现分组字段和统计函数，而其他的非分组字段不能使用。 SELECT deptno,ename,COUNT(empno) FROM emp GROUP BY deptno ;（错误的原因是ename不是分组字段） SELECT deptno, COUNT(empno) FROM emp GROUP BY deptno ;
统计函数嵌套使用——SELECT子句（不能出现任何字段，包括分组字段）	统计函数允许嵌套使用，但是嵌套统计函数之后的SELECT子句之中不允许再出现任何的字段，包括分组字段。 SELECT deptno,MAX(AVG(sal)) FROM emp GROUP BY deptno ; SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno ;

例子1：查询每个部门的名称、部门人数、部门平均工资、平均服务年限

• 平均服务年限需要计算年，使用MONTHS_BETWEEN函数
• 分析：
• 确定需要的数据表
• dept表：部门名称
• emp表：部门人数，平均工资，平均服务年限，使用统计函数计算
• 确定已知的关联字段
• 雇员和部门关联：emp.deptno=dept.deptno

dept表和emp表多表查询，暂时不分组	SELECT d.dname,e.empno,e.sal,e.hiredate FROM dept d,emp e WHERE e.deptno(+)=d.deptno
观察dname字段，进行分组： SELECT中只能有分组字段dname和统计函数AVG	SELECT d.dname, COUNT(e.empno) , AVG(e.sal) avgsal,       AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate) / 12)avgyear FROM dept d,emp e WHERE e.deptno(+)=d.deptno GROUP BY d.dname;
数字处理ROUND	SELECT d.dname, COUNT(e.empno) , ROUND(AVG(e.sal),2) avgsal,       ROUND(AVG(MONTHS_BETWEEN(SYSDATE,e.hiredate) / 12),2) avgyear FROM dept d,emp e WHERE e.deptno(+)=d.deptno GROUP BY d.dname;

 

例子2：查询出公司各个工资等级雇员的数量、平均工资

• 分析：
• 确定需要的数据表
• salgrade表：工资等级
• emp表：雇员信息，使用统计函数计算
• sal表：工资信息，使用统计函数平均工资
• 确定已知的关联字段
• 雇员和工资等级关联：emp.sal BETWEEN salgrade.losal AND salgrade.hisal

使用salgrade表和emp关联查询	SELECT .s.grade,e.empno,e.sal FROM emp e,salgrade s WHERE e.sal=BETWEEN s.losal AND s.hisal
观察grade字段，进行分组： SELECT中只能有分组字段grade和统计函数AVG	SELECT .s.grade,COUNT(e.empno),AVG(e.sal) FROM emp e,salgrade s WHERE e.sal=BETWEEN s.losal AND s.hisal ORDER BY s.grade
数字处理ROUND	SELECT s.grade,COUNT(e.empno), ROUND(AVG(e.sal),2) FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade ;

以上两个例子是针对多表查询后的数据进行统计，根据返回结果进行分组，可以说是针对于临时表分组.只要是行列的组成结果一定是临时表

 

例子3：统计出领取奖金与不领取奖金的雇员的平均工资、平均雇佣年限、雇员人数。

按照常规思路：从结果中可以看到，comm分组会将没有奖金和各种奖金等分为一组

SELECT comm, COUNT(EMPNO),ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp GROUP BY comm;

      COMM COUNT(EMPNO)     AVGSAL    AVGYEAR

---------- ------------ ---------- ----------

                     10     2342.5      34.82

      1400            1       1250      35.83

       500            1       1250      36.43

         0            1       1500      35.88

 

更换思路：如果要实现如上要求就可以分别查询有领取奖金和没有领取奖金的人数，使用UNION连接.

1、找到有领取奖金的雇员平均工资、平均雇佣年限、雇员人数

SELECT '领取奖金', COUNT(EMPNO),ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp WHERE comm IS NOT NULL;

2、找到不领取奖金的雇员平均工资、平均雇佣年限、雇员人数

SELECT '不领取奖金', COUNT(EMPNO),ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp WHERE comm IS NULL;

3、联合使用UNION

SELECT '领取奖金', COUNT(EMPNO),ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp WHERE comm IS NOT NULL

UNION

SELECT '不领取奖金', COUNT(EMPNO),ROUND(AVG(sal),2) avgsal, ROUND(AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12),2) avgyear FROM emp WHERE comm IS NULL;

'领取奖金'      COUNT(EMPNO)     AVGSAL    AVGYEAR

--------------- ------------ ---------- ----------

不领取奖金                10     2342.5      34.82

领取奖金                   3    1333.33      36.05

 

三、多字段分组（GROUP BY）

多字段分组是GROUP BY定义多个字段，且多个字段必须同时重复。

语法：

SELECT [DISTINCT] 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] | 统计函数 [AS] [别名] , ….

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[ORDER BY 排序字段 ASC|DESC] ;

例子：现在要求查询出每个部门的详细信息。

分析：部门编号、名称、位置、平均工资、最高工资、部门人数

确定需要的数据表：

emp表：统计平均工资、总工资等

dept表：编号，名称，位置

确定已知道的关联字段：

雇员和部门：emp.deptno=dept.deptno

1、将2张表进行关联，查询部门编号，名称，位置，雇员编号和姓名

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

FROM emp e,dept d

WHERE e.deptno=d.deptno;

2.现在可以看到三个列的数据整体都在重复，那么就具备了分组的条件，使用多字段分组，显示统计信息.

SELECT d.deptno ,d.dname,d.loc,COUNT(e.empno) count,

ROUND(AVG(sal),2) avg,

SUM(sal) sum, MAX(sal) max, MIN(sal) min

FROM emp e,dept d

WHERE e.deptno=d.deptno

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

3.要求列出所有部门，要使用外连接

SELECT d.deptno ,d.dname,d.loc,COUNT(e.empno) count,

ROUND(AVG(sal),2) avg,

SUM(sal) sum, MAX(sal) max, MIN(sal) min

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

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

4、有的部门没有雇员信息，有些结果是null，使用NVL函数处理

SELECT d.deptno ,d.dname,d.loc,NVL(COUNT(e.empno),0) count,

NVL(ROUND(AVG(sal),2) ,0) avg,

NVL(SUM(sal) , 0) sum, NVL(MAX(sal),0) max , NVL(MIN(sal), 0) min

FROM emp e,dept d

WHERE e.deptno(+)=d.deptno

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

 

四、HAVING子句(和WHERE子句的区别)

使用GROUP BY子句可以实现数据的分组显示，但是在很多时候往往需要对分组之后的数据进行再次的过滤，而要想实现这样的功能就只能通过HAVING子句完成。为什么WHERE子句不能实现呢？因为根据各个子句执行的顺序，WHERE在ORDER BY之前就执行了.

WHERE:是在分组前使用， 不能使用统计函数

HAVING：是在分组后使用（必须使用GROUP BY），允许使用统计函数

各个子句的执行顺序：

1、FROM-->

2、WHERE -->

3、GROUP BY（HAVING子句）-->

4、SELECT-->

5、ORDER BY-->

 

HAVING子句一定要和GROUP BY 子句一起使用

语法：

SELECT [DISTINCT] 分组字段1 [AS] [列别名] , [分组字段2 [AS] [列别名] , …] | 统计函数 [AS] [别名] , ….

FROM 表名称1 [表别名1] , 表名称2 [表别名2] ….

[WHERE 条件(s)]

[GROUP BY 分组字段1 , 分组字段2 , ….]

[HAVING 过滤条件(s)]

[ORDER BY 排序字段 ASC|DESC] ;

 

例子1：查询出所有平均工资大于2000的职位信息、平均工资、雇员人数

SELECT job, ROUND(AVG(sal),2) , COUNT(empno)

FROM emp

GROUP BY job

HAVING AVG(sal)>2000 ;

例子2：列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。

分析：

• 确定所需要的数据表
• dept表：部门编号， 名称等
• emp表：统计信息
• 确定已知的关联字段
• 雇员和部门关联：emp.deptno=dept.deptno

1、将两张表关联查询

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

FROM emp e, dept d

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

2、分组

SELECT d.deptno, d.dname, d.loc, ROUND(AVG(e.sal),2), MIN(e.sal),MIN(e.sal)

FROM emp e, dept d

WHERE e.deptno(+)=d.deptno

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

3、对分组进行排序

SELECT d.deptno, d.dname, d.loc, COUNT(e.empno),ROUND(AVG(e.sal),2), MIN(e.sal),MIN(e.sal)

FROM emp e, dept d

WHERE e.deptno(+)=d.deptno

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

HAVING COUNT(e.empno)>1;

例子3：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列

1、显示非销售人员工作名称,从事统一工作雇员的月工资总和

SELECT DISTINCT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job;

2、满足从事同一工作的雇员，月工资合计大于5000

SELECT DISTINCT job,SUM(sal) FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000;

3、将结果排序

SELECT DISTINCT job,SUM(sal) sum FROM emp WHERE job<>'SALESMAN' GROUP BY job HAVING SUM(sal)>5000 ORDER BY sum;