Prayer

*******************************************************************************
子查詢
*******************************************************************************

-----------------------------------------------------------------------------------------------------
子查詢是一個在select查詢中含有其他的select語句，子查詢通常用在where子句中，即將一個查詢結果做為查詢的條件.
-----------------------------------------------------------------------------------------------------
統計表employees中所有工資小于平均工資的人數
select count(*)工資 from employees
where salary <
(select avg(salary) from employees)
圖:
----------------
工資
56
49
36

Select MIN(Salary),Manager_id
from Employee
group by Manager_id
having MIN(Salary) >
(Select MIN(Salary)
From Employee
where Manager_id=004);

注意在多表查詢中返回的值應該是一個唯一確定的值，而不應該是多中值。
select Employee_id,Last_name,Salary,Manager_id
from Employee
WHERE Salary>
(select Salary
from Employee
where Manager_id=003)
and
Last_name= (select Last_name
from Employee
where Last_name like 'mary');

注意在any，or，all的區別：
Select Salary,Manager_id,Last_name,Employee_id
from Employee
where Salary < any
(Select Salary
From Employee
where Manager_id=005);

Select Salary
From Employee
where Manager_id=005；返回的是2000，2500
就是在工資中小于2000.2500其中一個就可以了，而ALL則必須是小于2000和2500其中任何一個
。而or則是和and用在一起的.