資料庫高階查詢之子查詢

子查詢
在一條SQL語句中巢狀了SELECT查詢語句


//查詢最高薪水是誰？
  分步查詢：
  //查詢最高薪水值
  select max(salary) from emp_xu;//99999.99
  //根據最高薪水值找到對應的員工
  select ename,salary from emp_xu 
  where salary=99999.99;
  
  合併：
  select ename,salary from emp_xu 
  where salary=(
  select max(salary) from emp_xu
  );


非關聯子查詢
1)查詢最高薪水是誰
  select ename,salary from emp_xu 
  where salary=(
  select max(salary) from emp_xu
  );


非關聯子查詢執行過程：先執行子查詢，子查詢返回
的結果作為主查詢的條件，再執行主查詢。
子查詢只執行一次，若子查詢返回結果為多個值，
oracle會去掉重複值，將結果返回給主查詢。
特徵：子查詢是單獨的查詢語句，不依賴主查詢的


2)查詢最低薪水是誰
  分步查詢：
  //查詢最低薪水值
  select min(salary) from emp_xu;
  //根據最低薪水值找對應員工
  select ename,salary from emp_xu
  where salary=最低薪水值;
  
  合併：
  select ename,salary from emp_xu
  where salary=(
  select min(salary) from emp_xu
  );


3)誰的薪水比'張無忌'高
   分步查詢：
   //查詢'張無忌'薪水
   select salary from emp_xu 
   where ename='張無忌';
   
   //薪水大於'張無忌'薪水
   select  ename,salary from emp_xu
   where salary>'張無忌'薪水;
   
   合併：
   select  ename,salary from emp_xu
   where salary>(
   select salary from emp_xu 
   where ename='張無忌'
   );
   
4)查詢研發部有哪些職位   
   分步查詢：
   //找'研發部'對應的部門號
   select deptno from dept_xu 
   where dname='研發部';
   //根據部門號找對應的職位
   select deptno,position from emp_xu
   where deptno='研發部'部門號;
   
   合併：
   select deptno,position from emp_xu
   where deptno=(
   select deptno from dept_xu 
   where dname='研發部'
   );
   
5)誰的薪水比'張無忌'高(如果有多個'張無忌')
插入一條記錄：
insert into emp_xu values(1014,'張無忌','Clerk',
8000,600,sysdate,1013,null);//全部欄位


commit;


   select  ename,salary from emp_xu
   where salary>(
   select salary from emp_xu 
   where ename='張無忌'
   );//錯誤的，子查詢返回多個值，不能直接用'>'


   修改：方式一
   select  ename,salary from emp_xu
   where salary>(
   select max(salary) from emp_xu 
   where ename='張無忌'
   );


   修改：方式二
   select  ename,salary from emp_xu
   where salary>all(
   select salary from emp_xu 
   where ename='張無忌'
   );


6)查詢哪些人的薪水比'張無忌'高
  //比任意一個'張無忌'高即滿足題目要求
   //查詢最低薪水的'張無忌'
   select min(salary) from emp_xu 
   where ename='張無忌';
   //查詢薪水大於最低薪水'張無忌'
   select ename,salary from emp_xu
   where salary>最低薪水'張無忌';
   
   合併：
   select ename,salary from emp_xu
   where salary>(
   select min(salary) from emp_xu 
   where ename='張無忌'
   );
   
   select ename,salary from emp_xu
   where salary>any(
   select salary from emp_xu 
   where ename='張無忌'
   );//子查詢返回多值，大於任意一個即可，直接用
   >any來表示
   
7)誰和'郭靖'同部門，列出除了郭靖以外的名字(單值)   
  分步查詢：
  //查詢'郭靖'部門號
  select deptno from emp_xu
  where ename='郭靖';
  
  //根據部門號找對應的員工
  select deptno,ename from emp_xu
  where deptno='郭靖'部門號 and
  ename <>'郭靖';
  
  合併：  
  select deptno,ename from emp_xu
  where deptno=(
  select deptno from emp_xu
  where ename='郭靖'
  ) and ename <>'郭靖'; 
   
8)誰和'郭靖'同部門，列出除了郭靖以外的名字(多值)      
  select deptno,ename from emp_xu
  where deptno=any(
  select deptno from emp_xu
  where ename='郭靖'
  ) and ename <>'郭靖'; 
   
  select deptno,ename from emp_xu
  where deptno in(
  select deptno from emp_xu
  where ename='郭靖'
  ) and ename <>'郭靖';//=any可以直接用in來代替


9)誰是'張三丰'的下屬(單值)
  分步查詢：
  //查詢'張三丰'員工號
  select empno from emp_xu
  where ename='張三丰';
  //誰的leader欄位等於'張三丰'員工號
  select ename from emp_xu
  where leader='張三丰'員工號;
  
  合併：
  select ename,leader from emp_xu
  where leader=(
  select empno from emp_xu
  where ename='張三丰'
  );
  
  多值：
  select ename,leader from emp_xu
  where leader=any(
  select empno from emp_xu
  where ename='張三丰'
  );
  
  select ename,leader from emp_xu
  where leader in(
  select empno from emp_xu
  where ename='張三丰'
  );


總結：
根據子查詢返回結果的行數選擇比較運算子：
返回一行(單值):> >= < <= =
返回多行(多值):>all >any in 


10)查詢每個部門拿最高薪水的是誰
 分步查詢：
 //每個部門最高薪
 select deptno,max(salary)
 from emp_xu
 where deptno is not null
 group by deptno;
 
 //根據部門號和最高薪找對應的員工
 select ename,deptno,salary 
 from emp_xu
 where (deptno,salary) in 每個部門最高薪水;
 
 合併：
 select ename,deptno,salary 
 from emp_xu
 where (deptno,salary) in (
 select deptno,max(salary)
 from emp_xu
 where deptno is not null
 group by deptno
 );//子查詢返回的是多值多列
 
 注意：子查詢返回的結果是單列還是多列，主查詢
 不關心，主查詢只關心子查詢返回的是單值還是多值
 問題。關鍵是能分清楚子查詢返回的行數，決定使用
 合適運算子。
 
11)哪個部門的人數比部門30號的人數多
  分步查詢：
  //30號部門人數
  select count(*) from emp_xu where deptno=30;
  
  //每個部門人數，要求大於30號部門人數
  select deptno,count(*)
  from emp_xu
  where deptno is not null
  group by deptno
  having count(*)>部門30號的人數;
  
  合併：
  select deptno,count(*)
  from emp_xu
  where deptno is not null
  group by deptno
  having count(*)>(
  select count(*) from emp_xu where deptno=30
  );//子查詢出現having語句中的


12)哪個部門的平均薪水比部門20號的平均薪水高，沒有
部門的不算在內
  分步查詢：
  //部門20號的平均薪水
  select avg(nvl(salary,0)) from emp_xu 
  where deptno=20;
  
  //每個部門平均薪水，要求薪水大於20號部門平均薪水
  select deptno,avg(nvl(salary,0)) from emp_xu
  where deptno is not null
  group by deptno
  having avg(nvl(salary,0))>部門20號平均薪水;
  
  合併：
  select deptno,avg(nvl(salary,0)) from emp_xu
  where deptno is not null
  group by deptno
  having avg(nvl(salary,0))>(
  select avg(nvl(salary,0)) from emp_xu 
  where deptno=20
  );


13)查詢員工所在部門平均薪水大於5000的員工姓名
和職位
 分步查詢：
 //查詢平均薪水大於5000的部門號
 select deptno from emp_xu
 where deptno is not null
 group by deptno
 having avg(nvl(salary,0))>5000;//多值
 
 //根據部門號找對應的員工姓名和職位
 select ename,position
 from emp_xu
 where deptno in 平均薪水大於5000的部門號;
 
 合併：
 select ename,position,deptno
 from emp_xu
 where deptno in (
 select deptno from emp_xu
 where deptno is not null
 group by deptno
 having avg(nvl(salary,0))>5000
 );


14)哪些員工的薪水是本部門的平均薪水值
 分步查詢：
 //每個部門的平均薪水
 select deptno,avg(nvl(salary,0))
 from emp_xu
 where deptno is not null
 group by deptno;//多值多列
 
 //根據部門號和平均薪水找對應的員工資訊
 select deptno,ename,salary
 from emp_xu
 where (deptno,salary) in (每個部門的平均薪水);
 
 合併：
 select deptno,ename,salary
 from emp_xu
 where (deptno,salary) in (
 select deptno,avg(nvl(salary,0))
 from emp_xu
 where deptno is not null
 group by deptno
 );






關聯子查詢
子查詢不再是獨立SQL語句，需要依賴主查詢傳過的
引數


1)哪些員工的薪水比本部門的平均薪水低
部門號相同，薪水小於平均薪水，兩者的比較規則不
一樣，不能直接用非關聯子查詢


select e.deptno,ename,salary
from emp_xu e
where salary<(
select avg(nvl(salary,0)) from emp_xu
where deptno is not null and deptno=e.deptno
);//子查詢依賴主查詢傳遞過來的引數e.deptno


關聯子查詢執行過程：
先執行主查詢，把主查詢的引數傳給子查詢，再執行
子查詢，子查詢返回的結果作為主查詢的條件，再
執行主查詢。子查詢是執行多次的。子查詢依賴主
查詢的。


2)查詢哪些人有下屬
非關聯子查詢：
select ename from emp_xu
where empno=any(
select leader from emp_xu
);


select ename from emp_xu
where empno in(
select leader from emp_xu
);
關聯子查詢：exists關鍵字
exists判斷存在某種關係：員工的員工號是別人的
leader欄位，則表示該員工有下屬


select ename from emp_xu e
where exists(
select 1 from emp_xu
where leader=e.empno
);


注意：exists關鍵字判斷子查詢有沒有資料返回，有
則為true,沒有則為false。exists不關心子查詢返回
的結果，所以子查詢中select後面寫什麼都可以。我
們一般直接用常量'1'表示。


3)查詢哪些人沒有下屬
非關聯子查詢
select ename from emp_xu
where empno not in(
select leader from emp_xu
where leader is not null
);//not in表示判斷不在列表項中，要求全部滿足
即可，將列表項中空值情況去掉


注意：not in(列表項)：如果列表中有空值，將沒有
結果返回(未選定行)；in(列表項)：如果列表中有
空值對結果沒有影響。


關聯子查詢：
select ename from emp_xu e
where not exists(
select 1 from emp_xu
where leader=e.empno
);


4)查詢哪些部門有員工
非關聯子查詢:
select deptno,dname from dept_xu
where deptno in(
select distinct deptno from emp_xu
);


關聯子查詢：
select deptno,dname from dept_xu
where exists(
select 1 from emp_xu 
where deptno=dept_xu.deptno
);


5)查詢哪些部門沒有員工
insert into dept_xu values(50,'後勤部','廣州');
commit;


非關聯子查詢：
select deptno,dname from dept_xu
where deptno not in(
select distinct deptno from emp_xu
where deptno is not null
);


關聯子查詢：
select deptno,dname from dept_xu
where not exists(
select 1 from emp_xu 
where deptno=dept_xu.deptno
);