组函数及分组统计

小鱼儿 2022-08-01 11:18 188阅读 0赞

# 分组函数 #

SQL中常用的分组函数

Count():  计数

Max()：求最大值

Min()：求最小值

Avg()：求平均值

Sum()：求和

-- 统计emp表中的人数
    select count(*) from emp; 
    
    -- 统计获得奖金的人数
    select count(comm) from emp;
    
    -- 求所有雇员的最低工资
    select min(sal) from emp;
    
    -- 求所有雇员的最高工资
    select max(sal) from emp;
    
    -- 求部门编号为20的雇员的平均工资和总工资
    select avg(sal),sum(sal) from emp where deptno = 20;

# 分组统计查询 #

语法格式

SELECT \{DISTINCT\}\*|查询列1 别名1，查询列2 别名2……

FORM 表名称1 别名1,表名称2 别名2,……

\{WHERE 条件表达式\}

\{GROUP BY 分组条件\}

\{ORDERBY  排序字段 ASC|DESC,排序字段 ASC|DESC,……\}

-- 统计出每个部门的人数
    select deptno,count(empno) from emp group by deptno;
    
    -- 求出每个部门的平均工资
    select deptno, avg(sal) from emp group by deptno;

统计每个部门的最高工资，以及获得最高工资的雇员姓名

如果写成

SELECT ename,max(sal)
     FROM emp
     GROUP BY deptno

Oracle会提示第 1 行出现错误:

ORA-00979: 不是 GROUP BY 表达式

以上代码在执行过程中出现错误，是因为:

1. 如果程序中使用了分组函数，则在以下两种情况下可以正常查询结果:

**程序中存在了GROUP BY,并指定了分组条件，这样可以将分组条件一起查询出来**

**      如果不使用GROUP BY,则只能单独地使用分组函数**

2．**使用分组函数时，查询结果列不能出现分组函数和分组条件之外的字段**

综上所述，我们在进行分组统计查询时有遵循这样一条规律:

**出现在字段列表中的字段，如果没有出现在组函数中，就必定出现在GROUP BY 语句的后面**

-- 统计出每个部门的最高工资，及最高工资的雇员姓名
    select deptno, ename,sal from emp where sal in(select max(sal) from emp group by deptno);

![SouthEast][]

-- 查询出每个部门的部门名称，及每个部门的雇员人数
    select d.dname, count(e.empno)
    from emp e, dept d
    where e.deptno = d.deptno
    group by d.dname

求出平均工资大于2000的部门编号和平均工资

初学者很容易错误地写成将工资大于2000的条件写在where的后面

SELECT deptno,avg(sal)  FROM emp  WHERE avg(sal)>2000   GROUP BYdeptno<span style="font-family:SimSun;"></span>

系统出现如下错误提示:

ORA-00934:  此处不允许使用分组函数

-- 求出平均工资大于2000的部门编号和平均工资
    select e.deptno, avg(sal)
    from emp e, dept d
    where e.deptno = d.deptno
    having avg(sal) > 2000
    group by e.deptno;

规则：WHERE 只能对单条记录限制（过滤），having是对分组进行过滤

分组函数只能在分组中使用，不能在WHERE语句之中出现，如果要指定分组条件，则只能通过第二种条件的指令：HAVING

-- 显示非销售人员工作名称以及从事同一工作雇员的月工资总和，并且要满足从事同一工作的雇员的月工资合计大于$5000,输出结果按月工资合计升序排列
    select e.job, sum(e.sal) sum_sal
    from emp e
    where e.job <> 'SALESMAN'
    group by e.job
    having sum(e.sal) > 5000
    order by sum_sal;

分组的简单原则：  
     **只要一列上存在重复内容才有可能考虑到用分组查询**

注意：

**分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的列名**

例：求平均工资最高的部门编号、部门名称、部门平均工资

第一步：

select deptno, avg(sal) from emp group by deptno;

![SouthEast 1][]

第二步：

select deptno, max(avg(sal)) from emp group by deptno;

ORA-00937: 不是单组分组函数

第三步：去掉查找结果中的deptno列

select max(avg(sal)) from emp group by deptno;

![SouthEast 2][]

逐步完成后：

select d.deptno, d.dname, t.avg_sal
      from dept d,
       (select deptno,avg(sal) avg_sal
          from emp
           group by deptno having avg(sal)=
              (select max(avg(sal)) from emp group by deptno) 
       ) t
    where t.deptno=d.deptno;

![SouthEast 3][]

[SouthEast]: /images/20220731/067a2d2357e74834a45fe3942be5e561.png
[SouthEast 1]: /images/20220731/0b6d3516a95042b58cc4b3e54109c62b.png
[SouthEast 2]: /images/20220731/076b9c7285c34030a01c7a9a95d04a02.png
[SouthEast 3]: /images/20220731/d21ff04164984b16af0ac360e238e67e.png