2019-03-27

-- any 任何一个

-- 查询高于10部门任意一名员工

select *

from emp

where sal > any(select sal from emp where deptno = 10)

-- all 全部

select *

from emp

where sal >all (select sal from emp where deptno = 20)

-- 因为大于小于号是单行运算符 所以当查询返回一条以上记录时 不能用><等符号

-- 查询员工工资为其他部门最低工资的员工编号和姓名及工资

select empno,ename,sal

from emp

where(deptno,sal) in(select deptno,min(sal)from emp group by deptno)

-- 查询比自己部门平均工资高的员工姓名 工资 部门平均工资

select ename,sal,avgsal

from emp e

join(select deptno,avg(sal) avgsal from emp group by deptno) d

on d.deptno = e.deptno

where sal > avgsal

-- 显示每个部门的最高工资的员工

select empno,ename,sal

from emp e

join(select deptno,max(sal) maxsal

from emp group by deptno)d

on e.deptno = d.deptno

where sal = maxsal

-- 相关子查询

-- 显示每个部门的最高工资的员工

select empno,ename,sal,deptno

from emp e

where sal = (select max(sal) from emp where deptno = e.deptno)

-- 显示部门名称和人数

select dname,(select count(empno) from emp where deptno = d.deptno) renshu

from dept d

select empno,ename,sal,deptno

from emp e

where(select count(*) from emp where deptno = e.deptno and sal>e.sal) = 0