了解
-- any 了解
select ename,sal
from emp
where empno =any (select mgr from emp)
-- 查询不是经理的员工姓名,工资
-- 注意:not in (参数列表)参数中不能有null
select ename,sal
from emp
where empno in (select mgr from emp where mgr is not NULL)
-- 查询员工工资高于10部门任意一名员工
select *
from emp
where sal >any(select sal from emp where deptno=10)
-- all 了解
-- 查询员工工资高于10部门所有员工
select *
from emp
where sal >all(select sal from emp where deptno=10)
-- 当子查询返回1条以上记录时,不能用>,=,<
多列子查询
-- 多列子查询
-- 9.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select empno ,ename,sal
from emp
where (deptno,sal) in (select deptno,min(sal) from emp group by deptno)
-- 16. 显示每个部门的最高工资的员工
select empno ,ename,sal,deptno
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
相关子查询
-- 相关子查询(高级子查询)
-- 查询每个部门工资最高的员工姓名,工资
elect empno ,ename,sal
from emp
where (deptno,sal) in (select deptno,max(sal) from emp group by deptno)
网友评论