【转】使用相关子查询

转自：http://software.it168.com/manual/sqlserver/ac_8_qd_11_4e2b.htm
        http://www.fzs8.net/database/2007-07-03/7583.html
相关子查询与普通子查询的区别在于：相关子查询引用了外部查询中的列！这种用外部查询来限制子查询的方法使SQL查询变得更加强大和灵活。因为相关子查询能够引用外部查询，所以它们尤其适合编写复杂的where条件！
相关子查询不能自己单独运行，其执行顺序如下：
1.首先执行一次外部查询
2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
3.使用子查询的结果来确定外部查询的结果集。

1.相关子查询

许多查询都可以通过执行一次子查询并将结果值代入外部查询的 WHERE 子句进行评估。在包括相关子查询（也称为重复子查询）的查询中，子查询依靠外部查询获得值。这意味着子查询是重复执行的，为外部查询可能选择的每一行均执行一次。

下面的查询查找获得某本书 100 % 共享版税 (royaltyper) 的所有作者名。

USE pubsSELECT au_lname, au_fnameFROM authorsWHERE 100 IN   (SELECT royaltyper   FROM titleauthor   WHERE titleauthor.au_ID = authors.au_id)

下面是结果集：

au_lname                                 au_fname             
---------------------------------------- -------------------- 
White                                    Johnson              
Green                                    Marjorie             
Carson                                   Cheryl               
Straight                                 Dean                 
Locksley                                 Charlene             
Blotchet-Halls                           Reginald             
del Castillo                             Innes                
Panteley                                 Sylvia               
Ringer                                   Albert               
(9 row(s) affected)

与以前提到的大多数子查询不同，该语句中的子查询无法独立于主查询而得到解决。该子查询需要一个 authors.au_id 值，而该值是个变量，随 Microsoft® SQL Server™ 检查 authors 表中的不同行而更改。

下面准确说明如何评估该查询：SQL Server 考虑 authors 表中的每一行是否都包括在结果中，方法是将每一行的值都代入内部查询中。例如，如果 SQL Server 首先检查 Cheryl Carson 行，那么变量 authors.au_id 将取值 238-95-7766，SQL Server 将该值代入到内部查询中。

USE pubsSELECT royaltyperFROM titleauthorWHERE au_id = '238-95-7766'

结果为 100，所以外部查询评估为：

USE pubsSELECT au_lname, au_fnameFROM authorsWHERE 100 IN (100)

由于这是真的，因此 Cheryl Carson 行包括在结果中。对 Abraham Bennet 行运行相同的过程，会发现该行没有包括在结果中。

2.使用别名的相关子查询
相关子查询可以用于从外部查询引用的表中选择数据之类的操作中。在这种情况下，必须使用表的别名（也称为相关名）明确指定要使用哪个表引用。例如，可以使用相关子查询查找已由多个出版商出版的书的类型。需要用别名来区分在其中出现 titles 表的两个不同角色。

USE pubs
SELECT DISTINCT t1.type
FROM titles t1
WHERE t1.type IN
   (SELECT t2.type
   FROM titles t2
   WHERE t1.pub_id <> t2.pub_id)

下面是结果集：

type
----------
business
psychology

(2 row(s) affected)

上面的嵌套查询等同于下面的自联接：

USE pubs
SELECT DISTINCT t1.type
FROM titles t1 INNER JOIN titles t2 ON t1.type = t2.type
   AND t1.pub_id <> t2.pub_id

3.HAVING 子句中的相关子查询

相关子查询还可以用于外部查询的 HAVING 子句。该结构可用于查找最高预付款超过给定组中平均预付款两倍的书籍类型。

在下例中，为外部查询中定义的每一个组各评估一次子查询（每次一种书籍类型）。

USE pubs
SELECT t1.type
FROM titles t1
GROUP BY t1.type
HAVING MAX(t1.advance) >=ALL
   (SELECT 2 * AVG(t2.advance)
   FROM titles t2
   WHERE t1.type = t2.type)

下面是结果集：

type
--------
mod_cook

(1 row(s) affected)

4.使用比较运算符的相关子查询
使用带有比较运算符的相关子查询可以查找销售数量低于该书平均销售数量的销售。
USE pubsSELECT s1.ord_num, s1.title_id, s1.qtyFROM sales s1WHERE s1.qty <   (SELECT AVG(s2.qty)   FROM sales s2   WHERE s2.title_id = s1.title_id)
下面是结果集：
ord_num              title_id  qty    
-------------------- -------- ------ 
6871                 BU1032   5      
722a                 PS2091   3      
D4482                PS2091   10     
N914008              PS2091   20     
423LL922             MC3021   15     
(5 row(s) affected)
外部查询依次选择 sales 的行（即 s1）。子查询为外部查询中的选择计算正在考虑的每个销售的平均数量。对于 s1 的每个可能值，如果数量小于计算的平均值，Microsoft® SQL Server™ 将评估子查询并将所考虑的记录放入结果中。
有时，相关子查询与 GROUP BY 子句极相似。下面的示例查找价格高于其同类书的平均值的所有书名。
USE pubsSELECT t1.type, t1.titleFROM titles t1WHERE t1.price >   (SELECT AVG(t2.price)   FROM titles t2   WHERE t1.type = t2.type)
下面是结果集：
type         title                                                       
------------ ----------------------------------------------------------- 
business     The Busy Executive's Database Guide                         
business     Straight Talk About Computers                               
mod_cook     Silicon Valley Gastronomic Treats                           
popular_comp But Is It User Friendly?                                    
psychology   Computer Phobic AND Non-Phobic Individuals: Behavior Variations                  
psychology   Prolonged Data Deprivation: Four Case Studies               
trad_cook    Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean                  
(7 row(s) affected)
对于 t1 每个可能的值，如果该行的 price 值大于计算的平均值，Microsoft_ SQL Server_ 将评估子查询并将该行放入结果中。由于计算平均价格的行受子查询中的 WHERE 子句限制，所以不必显式地根据类型分组。