分组统计_编写一个职位统计函数 jobstatistics(occupation: str),给定表格里的任一-优快云博客

本文介绍了SQL中的分组统计查询技巧，包括如何使用count、sum、avg等函数，以及group by和having子句的应用。通过具体示例展示了如何对数据进行有效分组，并对分组后的数据进行进一步筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

五个统计函数
count(*|[DISTINCT]字段)
max(字段)
min(字段)
sum(数字字段)
avg(数字字段).

sum()和avg()两个函数只要是数值型数据都可以使用.
查询所有雇员的总工资和平均工资

SQL> select sum(sal),avg(sal) from emp;

  SUM(SAL)   AVG(SAL)
---------- ----------
     29025 2073.21429

获取平均服务年限

SQL> select trunc (avg(months_between(sysdate,hiredate)/12))from emp;

TRUNC(AVG(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))
-----------------------------------------------
                                             34

Count(*);可以明确返回表中的个数,是最准确的
Count(字段)不统计null的数据段
Count(distinct 字段)消除重复数据后的字段.

分组统计查询
分组必须是有某些共性使用 GROUP BY子句完成

(4)语法:SELECT[DISTINCT] 分组字段[别名],..|统计函数|
(1)确定数据来源:SELECT*FROM 表名称[别名];
(2)确定满足条件的数据行:WHERE 过滤条件[WHERE(S)]
(3)针对于数据实现分组 GROUP BY 分组字段,分组字段
(4)针对于分组后的数据进行筛选 HAVING 分组后的过滤条件
(5)针对查询结果进行排序:[ORDER BY 字段 [ASC|DESC],字段[ASC|DESC],...];

按照职位分组,统计出每个职位的名称人数平均工资

SQL> select job,count(*),avg(sal) from emp group by job;

JOB                  COUNT(*)   AVG(SAL)
------------------ ---------- ----------
CLERK                       4     1037.5
SALESMAN                    4       1400
PRESIDENT                   1       5000
MANAGER                     3 2758.33333
ANALYST                     2       3000

查询出每个部门编号,以及每个部门的人数,最高与最低工资

SQL> select deptno, count(empno),max(sal),min(sal) from emp group by deptno;

    DEPTNO COUNT(ENAME)   MAX(SAL)   MIN(SAL)
---------- ------------ ---------- ----------
        30            6       2850        950
        20            5       3000        800
        10            3       5000       1300

**
分组统计查询限制
如果没有GROUP BY 字句,则在SELECT字句之中只允许出现统计函数,其他任何字段都不允许出现
在统计查询中之中,SELECT字句后只允许出现分组字段和统计函数,而在其他的费分组字段不能使用
统计函数允许嵌套使用,嵌套统计函数之后的SELECT字句之中不允许在出现任何的字段,包括分组字段
**
查询出每个部门的编号,名称,位置,平均服务年限
第一步确定要使用的数据表
dept表部门编号,名称,位置
emp表;部门人数,平均服务年限.
第二步确定已知的关联字段
雇员与部门关联:emp.deptno=dept.deptno;
第一步;查询出每个部门的编号,名称,位置.雇佣日期

SQL> select d.deptno,d.dname,d.loc,e.empno,e.hiredate
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno;

    DEPTNO DNAME                        LOC                             EMPNO HIREDATE
---------- ---------------------------- -------------------------- ---------- -------------
        10 ACCOUNTING                   NEW YORK                         7782 09-6月 -81
        10 ACCOUNTING                   NEW YORK                         7839 17-11月-81
        10 ACCOUNTING                   NEW YORK                         7934 23-1月 -82
        20 RESEARCH                     DALLAS                           7566 02-4月 -81
        20 RESEARCH                     DALLAS                           7902 03-12月-81
        20 RESEARCH                     DALLAS                           7876 23-5月 -87
        20 RESEARCH                     DALLAS                           7369 17-12月-80
        20 RESEARCH                     DALLAS                           7788 19-4月 -87
        30 SALES                        CHICAGO                          7521 22-2月 -81
        30 SALES                        CHICAGO                          7844 08-9月 -81
        30 SALES                        CHICAGO                          7499 20-2月 -81
        30 SALES                        CHICAGO                          7900 03-12月-81
        30 SALES                        CHICAGO                          7698 01-5月 -81
        30 SALES                        CHICAGO                          7654 28-9月 -81
        40 OPERATIONS                   BOSTON

通过以上字段可以发现,此时三个字段(deptno,dname,loc)上都有重复,所以使用group by
分组的时候,由于其多字段分组,那么我们就可以再次应用次概念

SQL> select d.deptno,d.dname,d.loc,count(e.empno),avg(Months_between(sysdate,e.hiredate)/12)year
  2  from emp e,dept d
  3  where e.deptno(+)=d.deptno
  4  group by d.deptno,d.dname,d.loc;

    DEPTNO DNAME                        LOC                        COUNT(E.EMPNO)       YEAR
---------- ---------------------------- -------------------------- -------------- ----------
        20 RESEARCH                     DALLAS                                  5 33.5350434
        40 OPERATIONS                   BOSTON                                  0
        10 ACCOUNTING                   NEW YORK                                3 35.5093061
        30 SALES                        CHICAGO                                 6 35.8081412

多字段分组的时候只有多个列的数据完全重复的时候才可以使用

查询出平均工资高于2000的职位名称以及工资

SQL> select job,avg(sal) from emp where avg(sal)>2000;
select job,avg(sal) from emp where avg(sal)>2000
                                   *
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数

出现此类错误是因为统计的操作是GROUBY之后的范畴,而where是在Group by操作之前使用的
所以只能够利用HAVING子句完成

(5)语法:SELECT[DISTINCT]*|;列名称[别名],列名称[别名]
(2)确定数据来源:SELECT
(1)FROM 表名称[别名];
(2)确定满足条件的数据行:WHERE 过滤条件[WHERE(S)]
(4)针对于分组后的数据进行筛选 HAVING 分组后的过滤条件
(6)针对查询结果进行排序:[ORDER BY 字段 [ASC|DESC],字段[ASC|DESC],...];

where于having的区别
where发生在GROUP by 操作之前,属于分组前的数据筛选,即:从所有的数据筛选出可用的数据
where字句不允许使用统计函数
Having 发生在group by操作之后是针对于分组后的数据筛选,HAVING字句可以使用统计函数

范例:显示非销售工资名称以及从事同一工作雇员的月工资的总和,并且要满足于 $5000,输出结果按月工资的合计升序排列第一步,显示非销售人员 select *from emp where job<>’SALESMAN’; 第二步:按照工作分组,统计出每个工作总和 select job,sum(sal)from emp where job<>’SALESMAN’ group by job; 第三步:从事同一工作雇员的月工资的总和,并且要满足于$ 5000
select job,sum(sal)from emp
where job<>’SALESMAN’
group by job
having sum(sal)>5000;
第四步按月工资的合计升序排列

SQL> select job,sum(sal)from emp
  2   where job<>'SALESMAN'
  3   group by job
  4   having sum(sal)>5000
  5   ORDER BY sum(sal);

JOB                  SUM(SAL)
------------------ ----------
ANALYST                  6000
MANAGER                  8275

总结
不管是单表分组还是多表分组,重点先看重复的列,如果是一个重复的列
就写多个字段
分组重点而使用限制
分组函数嵌套后不能够出现任何字段
分组函数可以单独使用,如果使用group by 才可以在select字句里面出现的字段
多表查询与分组统计的时候,查询结果相当于是是一张临时表,所有的分组是在临时表里面完成的