-- 子查询（包括非关联子查询和关联子查询）

-- 1、非关联子查询 子查询可以直接执行；

  -- 标量子查询 子查询的结果是一个单行单列的值；

select *

  from emp p

where p.sal=(select max(e.sal)

from emp e);

  -- 列子查询 子查询结果是一列；

select *

  from dept d

where d.deptno in(select distinct e.deptno

from emp); 

  -- 行子查询 子查询结果为一行数据

select *

  from emp p

where (p.deptno,p.sal)=(select e.deptno,

max(e.sal)

  from emp e

  group by e.deptno

having e.deptno=30

);

  -- 表子查询 查询每个部门薪资最高的员工信息 子查询的能做的基本上表连接都可以做到

select p.*

  from emp p

  join (select e.deptno,

  max(e.sal) as max_sal

  from emp e

group by e.deptno

  ) s

on p.deptno=s.deptno

  and p.sal=s.max_sal;

-- 2、关联子查询 子查询能否得出结果需要结合主查询的相应条件；主查询与子查询之间存在关联

  -- 查询部门存在员工的部门信息

select * from dept d where d.deptno in(select distinct e.deptno from emp e);

select count(*) from emp e where e.deptno=10;

select * from dept d where (select count(*) from emp e where e.deptno=d.deptno)>0

--where扫描dept的第一行获取第一个deptno（10） 放到子查询里

select count(*) from emp e where e.deptno=10;--3 >0

--符合where的条件 保留10部门的数据

--where扫描dept的第二行获取第一个deptno（20） 放到子查询里

select count(*) from emp e where e.deptno=20;--5 >0

--符合where的条件 保留20部门的数据

--where扫描dept的第三行获取第一个deptno（30） 放到子查询里

select count(*) from emp e where e.deptno=30;--6 >0

--符合where的条件 保留30部门的数据

--where扫描dept的第四行获取第一个deptno（40） 放到子查询里

select count(*) from emp e where e.deptno=40;--0=0

--符合where的条件 不保留40部门的数据

--最终得到的结果是10，20,30部门的部门信息；

  -- 查询每个部门最大薪资的员工信息（用关联子查询做）

select * from emp e where e.sal = (select max(p.sal) from emp p where p.deptno = e.deptno); 

  --非关联子查询+表连接可以做

select p.* from emp p

inner join

(select e.deptno,max(e.sal) as max_sal from emp e group by e.deptno) s

on p.deptno = s.deptno

and p.sal = s.max_sal;

  -- 列出至少有三个员工的所有部门和部门信息。

select * from dept d where (select count(*) from emp e where e.deptno = d.deptno)>=3;

  -- 非关联子查询+表连接可以做

select d.* from dept d inner join

(select e.deptno,count(*) rs from emp e group by e.deptno having count(*)>=3) s

on d.deptno = s.deptno