12.Oracle 高级查询-子查询

Oracle 高级查询-子查询

将一个查询结果作为基表再次进行查询，或者是将查询结果作为基表再和其他查询结果或者表再次联合！

子查询最为灵活，可以实现前面很多条件、投影、联合等查询！

但是，性能不高，对于书写要求高！

子查询的两种利用方式

1. 将一个查询结果作为基表再次进行查询

---

select * from (select * from emp where deptno = 10) where job = 'MANAGER';

-- 外层查询的列和条件是在子查询的结果集上进行的（所以列名以子查询结果的列定义为准）
select t.empno, t.ename, t.sal, t.job_name from
    (select empno, ename, sal, job job_name from emp where deptno = 10) t
        where t.job_name = 'MANAGER';

select * from
    (select min(sal) min_sal, max(sal) max_sal, deptno
        from emp
            where sal >= 1000
                group by deptno)
                    where max_sal - min_sal > 2000;

2. 将查询结果作为基表再和其他查询结果或者表再次联合

-- 求部门薪水差距超过2000的部门信息（名称、工作地）

select t.*, d.*
    from
        (select * from
            (select min(sal) min_sal, max(sal) max_sal, deptno
                from emp
                    where sal >= 1000
                        group by deptno)
                            where max_sal - min_sal > 2000
        ) t join dept d
            on t.deptno = d.deptno;

单值子查询

如果子查询的结果只有一行一列，那么该子查询的结果可以被作为一个值对待！

-- 查询薪水比FORD高的雇员
select * from emp where sal > (select sal from emp where ename = 'FORD');

-- 将SMITH的薪水改为和FORD一样
update emp
    set sal = (select sal from emp where ename = 'FORD')
        where ename = 'SMITH';

注意：返回结果必须是一行一列，否则报错

多值子查询

• 返回多行多列

-- 求每个部门内的最低薪水（返回部门编号和薪水值）
select deptno, min(sal)
    from emp
        group by deptno;

-- 求每个部门拿最低薪水的雇员信息
-- 两种写法：
-- 1、
select e.* from
    emp e join (
        select deptno, min(sal) min_sal
            from emp
                group by deptno
        ) t on e.deptno = t.deptno and e.sal = t.min_sal;

-- 2、
select * from emp where (deptno, sal) in (
                                            select deptno, min(sal)
                                                from emp
                                                    group by deptno);
select * from emp where (deptno, sal) =any (
                                            select deptno, min(sal)
                                                from emp
                                                    group by deptno);

• 返回多行单列

>all 大于一组值中的最大值
>any 比一组值中最小的值大
<all 小于一组值中的最小值
<any 比一组值中最大的值小

-- 查询比10号部门所有人薪水都要高的公司雇员
select * from emp where sal >all (select sal from emp where deptno  = 30);