Oracle_子查询
| 子查询 |
|
--如何查得所有比“CLARK”工资高的员工的信息
select ename, sal from emp where ename = 'CLARK';
select * from emp where sal>2450;
select *
from emp
where sal>(
select sal
from emp
where ename='CLARK'
);
--查询工资高于平均工资的雇员名字和工资
select ename, sal
from emp
where sal>(
select avg(sal)
from emp
);
--查询和SCOTT同一部门且比他工资低的雇员名字和工资
select ename,sal
from emp
where deptno=(
select deptno
from emp
where ename = 'SCOTT'
) and sal<(
select sal
from emp
where ename = 'SCOTT'
)
--查询工资最高的雇员名字和工资
select ename, sal
from emp
where sal=(
select max(sal)
from emp
); |
| any/all/in |
|
--查询工资低于任何一个“CLERK”的工资的雇员信息。
--any:与子查询结果的任意一条记录进行比较
select *
from emp
where sal < any(
select sal from emp where job='CLERK'
);
--查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
--all:与子查询结果的所有记录进行比较
select empno, ename, sal
from emp
where sal>all(
select sal
from emp
where job='SALESMAN'
)
--查询部门20中职务同部门10的雇员一样的雇员信息。
select *
from emp
where job in(
select job
from emp
where deptno=10
) and deptno=20; |
|
--查询在雇员中有那些人是有下属的
sekect *
from emp
where empno in(
select distinct mgr
from emp
);
--找出部门编号为20的所有员工中收入最高的职员
select *
from emp
where sal=(
select max(sal)
from emp
where deptno=20
) and deptno=20;
--查询每个部门的平均薪水等级
select deptno, avg(sal) from emp group by deptno;
select temp.deptno, temp.avg_sal, s.grade
from salgrade s, (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) temp
where temp.avg_sal between s.losal and s.hisal;
select temp.deptno, d.dname, temp.avg_sal, s.grade
from salgrade s, (
select deptno, avg(sal) avg_sal
from emp
group by deptno
) temp, dept d
where temp.avg_sal between s.losal and s.hisal
and temp.deptno = d.deptno; |