-- 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
