Oracle--子查询

 

求出谁的工资比 scott 工资高

1.先求出scott工资

select sal from emp
where ename = 'SCOTT';

2.查询谁的工资比scott高

 

select * 
from emp
where sal > (select sal from emp
where ename = 'SCOTT');

子查询的本质就是 select 语言的嵌套

使用子查询的场景：对于一步不能解决的问题，需要多步

案例--查询部门为sale的员工信息

select *
from emp
where deptno = (
        select deptno 
        from dept
        where dname = 'SALES'
);

使用多表查询

select e.*
from emp e, dept d
where e.deptno = d.deptno and d.dname = 'SALES';

可以在主查询的什么地方放一个子查询

 

        select a, b, c (select *...)    //ok    //在select 后面的子查询，必须是单行子查询
        from tab1 ,tab2, (select **)    //ok
        where ... (select *...)         //ok
        order by ...    //err
        group by ...    //err
        having ...(select ****)         //

 查询 和141号员工工种一样的，薪水比143号员工工资高的员工信息

        select last_name, job_id, salary
        from employees
        where job_id = (
                select job_id
                from employees
                where employee_id = 141
        )
        and salary > (
                select salary
                from employees
                where employee_id = 143
        );

 查询工资最低的员工信息

        select last_name, job_id, salary
        from employees
        where salary = (
                select MIN(salary)
                from employees
        );

求最低工资比50号部门最低工资的各个部门编号和部门最低工资

        select department_id, MIN(salary)
        from employees
        group by department_id
        having MIN(salary) > (
                select MIN(salary)
                from employees
                where department_id = 50
        );

 

多行查询:多行比较操作符

--查询部门为 SALES 和 ACCOUNTING 的员工信息

        select *
        from emp
        where deptno in (select deptno from dept where dname='SALES' or dname = 'ACCOUNTING');

--查询部门名称为 不是 SALES 和 ACCOUNTING 的员工信息

        select *
        from emp
        where deptno not in (select deptno from dept where dname='SALES' or dname = 'ACCOUNTING');

--查询薪水比 30 号部门所有员工薪资都高的员工信息

        select * 
        from emp
        where sal > (select max(sal) from emp where deptno = 30);

        select * 
        from emp
        where sal > all(select max(sal) from emp where deptno = 30);

--查询薪水比 30 号部门任意员工薪资都高的员工信息

        select * 
        from emp
        where sal > (select min(sal) from emp where deptno = 30);


        select * 
        from emp
        where sal > all(select min(sal) from emp where deptno = 30);

any/all 的运算和前面的逻辑比较关系相关

# 面试强化

--查询不是经理的员工信息

分析：先查询是经理的员工信息

        select *
        from emp
        where empno in (select mgr from emp);

在查询不是经理的员工信息且mgr列信息不为空

        select *
        from emp
        where empno not in (select mgr from emp where mgr is not null);

--查询员工表中工资最高的三个员工信息

        select empno, ename, sal
        from emp
        order by sal desc;

        select rownum, empno, ename, sal
        from emp
        where rownum <= 3
        order by sal desc

rownum:

        服务器返回的结果集的固有属性

        rownum 只能使用小于，不能大于

=====》top-N

        select rownum, empno, ename, sal from
        (
                select  empno, ename, sal
                from emp
                order by sal desc
        )
        where rownum <=3 ;

=====》分页操作：

select r, empno, ename, sal
from
        (
                select rownum r, empno, ename, sal from
                (
                        select  empno, ename, sal
                        from emp
                        order by sal desc
                )
        where rownum <= 8
        )
where r >= 5;

**解决分页的思路**

        思路：内层排序，外层选择

        内：排序

        中：使用rownum选择前n条，并给rownum指定一个别名，以供最外层过滤使用

        外：去掉前n层结果

--找到员工表中薪水大于本部门平均薪水的员工

### 多表查询

1.求各个部门的平均薪水水平

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

2.可以把部门平均薪水的数据，看成一个表，多表查询

select e.empno, e.ename, e.sal, d.avgsal
from emp e ,
        (
                select deptno , avg(sal) avgsal
                from emp
                group by deptno     
        ) d
where e.deptno = d.deptno and e.sal > d.avgsal;

### 方法2：子查询

        select empno, ename, sal, (员工所在部门的平均工资) avgsal

        from emp

        where sal > (员工所在部门的平均工资) avgsal

        员工所在部门的平均工资

        select e.empno, e.ename, e.sal, ( select avg(sal) avgsal from emp where deptno = e.deptno) avgsal
        from emp e
        where sal > ( select avg(sal) avgsal from emp where deptno = e.deptno);

主查询通过别名把著查询的值传递给子查询