记录

分组函数（高级查询）

  • 2022-07-16
  • 作者 我爱小小蝶
  • ~5.73K 字
  1. 1. 分组函数概述
  2. 2. 使用分组函数
  3. 3. 分组函数练习
  4. 4. GROUP BY分组练习
  5. 5. 用 HAVING 子句排除组结果
  6. 6. SELECT语句执行过程
  7. 7. 单行子查询
    1. 7.1. 练习
  8. 8. ANY 的使用
  9. 9. ALL的使用
  10. 10. 多行子查询
    1. 10.1. 练习
  11. 11. 子查询中的空值
  12. 12. 练习

分组函数概述

​ 分组函数是对表中一组记录进行操作，每组只返回一个结果，即首先要对表记录进行分组，然后再进行操作汇总，每组返回一个结果，分组时可能是整个表分为一组，也可能根据条件分成多组。

分组函数常用到以下五个函数：

  • MIN
  • MAX
  • SUM
  • AVG
  • COUNT

使用分组函数

1
2
3
4
5
6
SELECT	[column,] group_function(column)
FROM		table
[WHERE	condition]
[GROUP BY	column]
[HAVING       group_function(column)expression 
[ORDER BY	column| group_function(column)expression];

分组函数练习

1.查询部门20的员工，每个月的工资总和及平均工资。

1
2
3
select sum(sal),avg(sal)
from emp
where deptno = 20;

2.查询工作在CHICAGO的员工人数，最高工资及最低工资。

1
2
3
4
select count(*),max(sal),min(sal)
from emp,dept
where emp.deptno = dept.deptno
and loc = 'CHICAGO';

3.查询员工表中一共有几种岗位类型。

1
2
select count(distinct job)
from emp

GROUP BY分组练习

1.查询每个部门的部门号，部门名称，部门人数，最高工资，最低工资，工资总和，平均工资

1
2
3
4
select emp.deptno,dname,count(*),max(sal),min(sal),sum(sal),avg(sal)
from emp,dept
where emp.deptno = dept.deptno
group by emp.deptno,dname;

2、查询每个部门，每个岗位的部门编号，部门名称，岗位名称，部门人数，最高工资，最低工资，工资总和，平均工资 

1
2
3
4
select emp.deptno,dname,job,count(*),max(sal),min(sal),sum(sal),avg(sal)
from emp,dept
where emp.deptno = dept.deptno
group by emp.deptno,dname,job

3、查询每个经理所管理的人数，经理编号，经理姓名，要求包括没有经理的人员信息

1
2
3
4
5
select count(*),t1.mgr,t2.ename
from emp t1
left join emp t2
on t1.mgr = t2.empno
group by t1.mgr,t2.ename

用 HAVING 子句排除组结果

使用 HAVING 子句限制组
记录已经分组.
使用过组函数.
与 HAVING 子句匹配的结果才输出

不能在 WHERE子句中限制组
可以通过 HAVING 子句限制组

SELECT语句执行过程

  1. 通过FROM子句中找到需要查询的表；
  2. 通过WHERE子句进行非分组函数筛选判断；
  3. 通过GROUP BY子句完成分组操作;
  4. 通过HAVING子句完成组函数筛选判断；
  5. 通过SELECT子句选择显示的列或表达式及组函数；
  6. 通过ORDER BY子句进行排序操作。

1.查询部门人数大于2的部门编号，部门名称，部门人数。

1
2
3
4
5
select dept.deptno,dname,count(*)
from dept,emp
where dept.deptno = emp.deptno
group by dept.deptno,dname
having count(*) > 2

2.查询部门平均工资大于2000，并且人数大于2的部门编号
– 部门名称，部门人数，部门平均工资，并按照部门人数升序排列

1
2
3
4
5
6
select d.deptno,d.dname,count(*) count,avg(sal) avgSal 
from emp e, dept d 
where e.deptno=d.deptno 
group by d.deptno
having avgSal>2000 and count>2 
order by count;

3.查出比JONES工资高的其它员工

1
2
3
4
5
select *
from emp
where sal >(select sal
from emp
where ename = 'JONES')

单行子查询

子查询只返回一行一列
使用单行运算符（>、<、<> ……）

练习

1.查询入职日期最早的员工姓名，入职日期

1
2
3
select ename,hiredate
from emp
where hiredate = (select min(hiredate) from emp)

2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称

1
2
3
4
5
6
7
select ename,sal,dname
from emp,dept
where emp.deptno = dept.deptno
and loc = 'CHICAGO'
and sal > (select sal
from emp
where ename = 'SMITH')

3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期

1
2
3
4
5
6
select emp.ename,min(hiredate)
from emp
group by deptno
having min(hiredate) < (select min(hiredate)
from emp
where deptno = 20)

4.查询部门人数大于所有部门平均人数的部门编号，部门名称，部门人数

1
2
3
4
5
6
7
8
select dept.deptno,dept.dname,count(*)
from emp,dept
where emp.deptno = dept.deptno
group by deptno
having count(*) > (select avg(t.count)
from (select count(*) count
from emp
group by deptno)t)

ANY 的使用

1.查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资

1
2
3
select empno,ename,job,sal 
from emp 
where deptno<>10 and sal>any(select sal from emp where deptno=10)

ALL的使用

1.查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，姓名，职位，工资。

1
2
3
select empno,ename,job,sal 
from emp 
where deptno<>10 and sal<all(select sal from emp where deptno=10)

多行子查询

子查询返回记录的条数 可以是一条或多条。
和多行子查询进行比较时，需要使用多行操作符，多行操作符包括：
IN、ANY、ALL。

IN操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

练习

1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工

1
2
3
select ename,hiredate,deptno  
from emp 
where hiredate > any(select hiredate from emp where deptno=10) and deptno<>10;

2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工

1
2
3
4
5
6
select ename,hiredate
from emp
where hiredate <all (select hiredate
from emp
where deptno = 10)
and deptno <> 10

3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

1
2
3
4
select ename,job
from emp
where job in (select job from emp where deptno = 10)
and deptno <> 10;

子查询中的空值

1.查询不是经理的员工姓名。

1
2
3
select ename
from emp 
where empno not in (select mgr from emp);

问题：子查询返回的结果中含有空值。
上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会返回8条记录，但是却一条也没返回，why?

因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分，就不能使用NOT IN 运算符。

解决方法

1
2
3
select ename 
from emp
where  mgr not  in ((select empno from emp)) or mgr is NULL;

练习

1.查询部门平均工资在2500元以上的部门名称及平均工资。

1
2
3
4
select d.dname,avg(e.sal) 
from emp e,dept d 
where e.deptno=d.deptno and e.sal>2500
group by e.deptno

2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。

1
2
3
4
5
select job,avg(sal) 
from emp 
where job not like 'SA%' and sal>2500 
group by job 
order by sal desc;

3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。

1
2
3
4
select e.deptno,round(min(sal)),round(max(sal))
from emp e,dept d 
group by e.deptno 
having   count(e.empno)>=10;

4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。

1
2
3
4
select job,sum(sal)
from emp 
GROUP BY job
having job<>'SALESMAN' and sum(sal)>=2500

5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。

1
2
3
4
5
select m.empno,m.ename,e.mgr,min(e.sal)
from emp e, emp m
where e.mgr=m.empno and  e.sal>=3000 or e.mgr is null 
group by e.mgr 
order by e.sal desc;

6.查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。

1
2
3
4
5
select empno,ename,sal,job
from emp
where sal>(select sal from emp where empno=7782) 
and job=(select job from emp where empno=7369) 
and empno<>7369;

7.查询工资最高的员工姓名和工资。 

1
2
3
select ename,sal
from emp
where sal=(select max(sal) from emp);

8.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。

1
2
3
4
select empno,ename,sal,deptno 
from emp 
GROUP BY deptno
HAVING min(sal)>(select min(sal) from emp where deptno=10)

9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。

1
2
3
4
select empno,sal,deptno
from emp 
GROUP BY deptno
HAVING min(sal) and deptno is not null;

10.显示经理是KING的员工姓名，工资。

1
2
3
select empno,ename,sal,mgr
from emp 
where mgr=(select empno from emp where ename='KING')

11.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。

1
2
3
select ename,sal,hiredate
from emp
where hiredate>(select hiredate from emp where ename='SMITH');
最后编辑：2022-07-16