正文
Sql Server系列：子查询
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
1 子查询概念
子查询是嵌套在另一个查询中的普通T-SQL查询。在有一个SELECT语句通过使用小括号创建子查询，作为另一个查询的部分数据或条件的基础。
子查询通常用于满足以下某个需求：
◊ 将一个查询分解为一系列的逻辑步骤
◊ 提供一个列表作为WHERE子句或[IN | EXISTS | ANY |ALL]的目标
◊ 为父查询中的每个记录提供一个查询表
还值得注意的是，大部分（不是全部）子查询可以使用连接来编写。
2 构建嵌套子查询
嵌套子查询只在一个方向嵌套：返回在外部查询中使用的单个值，或者与IN运算符一起使用的一个完整的值列表。
子查询的查询语法类似以下两个语法模板之一：
SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> = (
SELECT <single column>
FROM <SomeTable>
WHERE <condition that results in only row returned>
)
SELECT <SELECT list>
FROM <SomeTable>
WHERE <SomeColumn> IN (
SELECT <single column>
FROM <SomeTable>
WHERE <condition>
)
2.1 使用返回单个值的SELECT语句的嵌套查询
查询UnitPrice大于等于10的CategoryName
SELECT DISTINCT [CategoryName]
FROM [dbo].[Category] c
INNER JOIN [dbo].[Product] p
ON c.[CategoryID] = p.[CategoryID]
WHERE p.[UnitPrice] >= 10
查询UnitPrice最小的记录
SELECT p.[ProductID], p.[ProductCode], p.[ProductName], c.[CategoryName], p.[UnitPrice]
FROM [dbo].[Category] c
INNER JOIN [dbo].[Product] p
ON c.[CategoryID] = p.[CategoryID]
WHERE p.[UnitPrice] = (
SELECT MIN([UnitPrice]) FROM [dbo].[Product]
)
2.2 使用返回多个值的子查询的嵌套查询SELECT [dbo].[Product].* FROM [dbo].[Product]
WHERE [CategoryID] IN (
SELECT [CategoryID] FROM [dbo].[Category] WHERE [CategoryID] <= 10
)
SELECT [dbo].[Product].* FROM [dbo].[Product]
WHERE [CategoryID] IN (
SELECT [CategoryID] FROM [dbo].[Category] WHERE [CategoryID] <= 10
)
尽管使用IN的嵌套查询方式可以实现，但更好的方式是使用内连接实现这种查询，而不是使用使用嵌套的SELECT。
上例的子查询使用INNER JOIN方式：
SELECT [dbo].[Product].* FROM [dbo].[Product]
INNER JOIN [dbo].[Category]
ON [dbo].[Product].[CategoryID] = [dbo].[Category].[CategoryID]
WHERE [dbo].[Category].[CategoryID] <= 10
出于性能方面的考虑，如果没有特别的理由要使用嵌套的SELECT，则还是应使用连接方式作为默认的解决方案。在大部分情况下，SQL Server会将嵌套子查询解决方案解析为和使用连接用于的查询计划，在检查嵌套子查询和内连接查询计划时，会发现它们的完全相同的计划。大多数情况下，这两种方法没有多大的区别。当查询计划不同时，连接通常的更好的选择。
3 关联子查询
关联子查询与嵌套子查询的不同之处在于信息传递是双向的，而不是单向的。在嵌套子查询中，内部查询只处理一次，然后将信息传递到外部查询，而外部查询也只执行一次。
在关联子查询中，内部查询用外部查询提供的信息运行，外部查询使用内部查询的信息运行。
关联查询分3个步骤进行的处理过程：
◊ 外部查询获得一个记录，将记录传递到内部查询；
◊ 内部查询根据传递的值执行；
◊ 内部查询将结果值返回外部查询，外部查询再利用这些值完成处理过程。
3.1 在WHERE子句中的关联子查询
示例：查询在Product中每个CategoryID中UnitPrice最贵的Product记录
实现方式一：使用两个单独的查询，先创建一个临时表，再进行连接。
-- 查询每个CategoryID中UnitPrice最大的记录
SELECT [CategoryID], MAX([UnitPrice]) AS [UnitPrice]
INTO #MaxUnitPriceProduct
FROM [dbo].[Product]
GROUP BY [CategoryID]-- SELECT * FROM #MaxUnitPriceProduct
-- 查询每个CategoryID中UnitPrice最大的Product记录
SELECT p.[ProductID], p.[CategoryID], p.[ProductCode], p.[ProductName], p.[UnitPrice]
FROM [dbo].[Product] p
INNER JOIN #MaxUnitPriceProduct t
ON p.[CategoryID] = t.[CategoryID]
AND p.[UnitPrice] = t.[UnitPrice]DROP TABLE #MaxUnitPriceProduct
实现方式二：使用关联子查询
SELECT p1.[ProductID], p1.[CategoryID], p1.[ProductCode], p1.[ProductName], p1.[UnitPrice]
FROM [dbo].[Product] p1
WHERE P1.[UnitPrice] = (
SELECT MAX(p2.[UnitPrice]) FROM [dbo].[Product] p2
WHERE p2.[CategoryID] = p1.[CategoryID]
)
3.2 在SELECT列表中的关联子查询SELECT p1.[ProductName], (
SELECT MAX([UnitPrice]) FROM [dbo].[Product] P2
WHERE P2.[CategoryID] = p1.[CategoryID]
) AS [UnitPrice]
FROM [dbo].[Product] P1
5 派生表
SELECT p1.[ProductName], (
SELECT MAX([UnitPrice]) FROM [dbo].[Product] P2
WHERE P2.[CategoryID] = p1.[CategoryID]
) AS [UnitPrice]
FROM [dbo].[Product] P1
派生表是有一个查询结果集的行和列构成，要创建派生表，需要做以下两件事情：
◊ 将生成结果集的查询用小括号括起来
◊ 给查询结果取别名，这样可以作为表被引用
语法结构
SELECT <select list>
FROM (<query that returns a regular resultset>) AS <alias name>
JOIN <some other base or derived table>
示例
SELECT p.[ProductID],p.[ProductCode],p.[ProductName],p.[UnitPrice],t.[CategoryName]
FROM [dbo].[Product] p
INNER JOIN (
SELECT [CategoryID],[CategoryName] FROM [dbo].[Category]
WHERE [CategoryID] <= 10
) t
ON p.[CategoryID] = t.[CategoryID]
