经典子查询收集

1.用于替代表达式的子查询
在 Transact-SQL 中，除了在 ORDER BY 列表中以外，在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何可以使用表达式的地方都可以使用子查询来替代。

下面的示例将说明如何使用该增强功能。该查询查找热门计算机书籍的价格、全部书的平均价格，以及每本书的价格与全部书的平均价格之间的差价。

USE pubsSELECT title, price,(SELECT AVG(price) FROM titles) AS average,price-(SELECT AVG(price) FROM titles) AS differenceFROM titlesWHERE type='popular_comp'
下面是结果集：

title                     price          average        difference      
------------------------  -------------- -------------- ----------------
But Is It User Friendly?  22.95          14.77          8.18            
Secrets of Silicon Valley 20.00          14.77          5.23            
Net Etiquette             (null)         14.77          (null)          
(3 row(s) affected)
2.
多层嵌套
子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。

下面的查询查找至少曾参与编写一本热门计算机书的作者姓名。

USE pubsSELECT au_lname, au_fnameFROM authorsWHERE au_id IN   (SELECT au_id   FROM titleauthor   WHERE title_id IN      (SELECT title_id      FROM titles      WHERE type = 'popular_comp'))下面是结果集：

au_lname                                 au_fname            
---------------------------------------- --------------------
Carson                                   Cheryl              
Dull                                     Ann                 
Locksley                                 Charlene            
Hunter                                   Sheryl              
(4 row(s) affected)
最内部的查询返回书名 ID 号 PC1035、PC8888 和 PC9999。再上一层的查询使用这些书名 ID 进行评估，并返回作者的 ID 号。最后，外部查询使用作者的 ID 号查找作者的姓名。

也可以使用一个联接表示该查询：

USE pubsSELECT au_lname, au_fnameFROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id   JOIN titles ON titleauthor.title_id = titles.title_idWHERE type = 'popular_comp'

3.
SELECT 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 子句极相似。下面的示例查找价格高于其同类书的平均值的所有书名。

SELECT 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 子句限制，所以不必显式地根据类型分组。