SQL嵌套子查询

短命女 2022-07-13 20:15 281阅读 0赞

自：http://blog.csdn.net/AJAXBloger/archive/2007/08/29/1764506.aspx

子查询：  
使用子查询的原则  
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;