萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> 實例簡析SQL嵌套子查詢

實例簡析SQL嵌套子查詢

   實例簡析SQL嵌套子查詢:

  一些初級程序員常常對SQL語法中的子查詢,由其對嵌套子查詢(子查詢中包含一個子查詢)的使用比較生疏,本文就此做一個基本講解,相信新手會有一定收獲。

  使用子查詢的原則

  1.一個子查詢必須放在圓括號中。

  2.將子查詢放在比較條件的右邊以增加可讀性。

  子查詢不包含 ORDER BY 子句。對一個 SELECT 語句只能用一個 ORDER BY 子句,

  並且如果指定了它就必須放在主 SELECT 語句的最後。

  ORDER BY 子句可以使用,並且在進行 Top-N 分析時是必須的。

  3.在子查詢中可以使用兩種比較條件:單行運算符和多行運算符。

  子查詢的類型

  單行子查詢:從內 SELECT 語句只返回一行的查詢

  多行子查詢:從內 SELECT 語句返回多行的查詢

  單行子查詢

  單行子查詢是從內查詢返回一行的查詢。在該子查詢類型中用一個單行操作符。幻燈片中列出了單行操作符。

  例

  顯示那些 job ID 與雇員 141 相同的雇員。

  SELECT last_name, job_id

  FROM employees

  WHERE job_id =

  (SELECT job_id

  FROM employees

  WHERE employee_id = 141);

  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);

  顯示 job ID 與雇員 141 相同,並且薪水 高於雇員 143 的那些雇員。

  注:外和內查詢可以從不同的表中取得數據。

  SELECT last_name, job_id, salary

  FROM employees

  WHERE salary =

  (SELECT MIN(salary)

  FROM employees);

  求所有人誰的工資最小。

  SELECT department_id, MIN(salary)

  FROM employees

  GROUP BY department_id

  HAVING MIN(salary) >

  (SELECT MIN(salary)

  FROM employees

  WHERE department_id = 50);

  求每個部門的最小工資,但是要高於50號部門的工資。

  SELECT employee_id, last_name

  FROM employees

  WHERE salary =

  (SELECT MIN(salary)

  FROM employees

  GROUP BY department_id);

  問題出現在:單行子查詢返回了多個查詢值;

  應改為:

  SELECT employee_id, last_name

  FROM employees

  WHERE salary in

  (SELECT MIN(salary)

  FROM employees

  GROUP BY department_id);

  SELECT last_name, job_id

  FROM employees

  WHERE job_id =

  (SELECT job_id

  FROM employees

  WHERE last_name = ’Haas’);

  如果子查詢返回的是零值,不會對主程序造成影響;

  如果子查詢返回的是空值,那麼會影響主程序的返回值;

  SELECT employee_id,

  last_name

  FROM employees

  WHERE employee_id NOT IN

  (SELECT manager_id

  FROM employees)

  SELECT employee_id,

  last_name

  FROM employees

  WHERE employee_id NOT IN

  (SELECT manager_id

  FROM employees

  WHERE manager_id IS NOT NULL)

  放在select下的子查詢的返回值必須是一個具體值,

  from後面也可以加子查詢;

  having後面也可以加子查詢;

  order by後面也可以;

  多列子查詢適應於:成對比較;非成對比較。

  SELECT employee_id, manager_id, department_id

  FROM employees

  WHERE (manager_id, department_id) IN

  (SELECT manager_id, department_id

  FROM employees

  WHERE employee_id IN (178,174))

  AND employee_id NOT IN (178,174);

  輸出:176 149 80

  只有要查詢的東西和你子查詢返回的東西一一對應上了,你的查詢才能成功。

  如果有一個 對應不上那麼你的查詢不會成功。

  非成對的子查詢:

  SELECT employee_id, manager_id, department_id

  FROM employees

  WHERE manager_id IN (SELECT manager_id

  FROM employees

  WHERE employee_id IN (174,141))

  AND department_id IN (SELECT department_id

  FROM employees

  WHERE employee_id IN (174,141))

  AND employee_id NOT IN(174,141);

  輸出:144 124 50

  143 124 50

  142 124 50

  176 149 80

  上面兩個程序就是成對子查詢和非成對子查詢兩者之間的區別。

  如果我想去顯示員工信息,要求:員工的工資高於本部門的平均工資。

  SELECT a.last_name,

  a.salary,

  a.department_id

  FROM employees a

  WHERE a.salary >

  (SELECT AVG(salary)

  FROM employees b

  WHERE b.department_id = a.department_id);

  in line view(內聯視圖)

  SELECT a.last_name, a.salary,

  a.department_id, b.salavg

  FROM employees a, (SELECT department_id,

  AVG(salary) salavg

  FROM employees

  GROUP BY department_id) b

  WHERE a.department_id = b.department_id

  AND a.salary > b.salavg;

  數據分散於多個表中需要進行拼合

  SELECT DISTINCT c.LastName, c.FirstName

  FROM Person.Contact c JOIN HumanResources.Employee e

  ON e.ContactID = c.ContactID WHERE EmployeeID IN

  (SELECT SalesPersonID

  FROM Sales.SalesOrderHeader

  WHERE SalesOrderID IN

  (SELECT SalesOrderID

  FROM Sales.SalesOrderDetail

  WHERE ProductID IN

  (SELECT ProductID

  FROM Production.Product p

  WHERE ProductNumber LIKE 'FW123%')));

copyright © 萬盛學電腦網 all rights reserved