Sql Server系列：子查詢

libingql發表於2015-04-26
SQLServer

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
)

　　儘管使用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 <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]

相關文章