子查詢

在嵌套查詢中，最外面查詢結果集返回給調用方，稱為外部查詢。嵌套在外部查詢內的查詢稱為子查詢，子查詢的結果集供外部查詢使用。

根據是否依賴外部查詢，可將子查詢分為自包含子查詢和相關子查詢。自包含子查詢不依賴外部查詢，相關子查詢則依賴外部查詢。

子查詢結果是在運行時計算的，查詢結果會跟隨查詢表的變化而改變。子查詢可以返回單個值（標量）、多個值或者整個表結果。

在邏輯上，子查詢代碼僅在外部查詢計算之前計算一次。

自包含子查詢

USE WJChi; ? SELECT * FROM dbo.UserInfo WHERE Age= (SELECT MAX(Age) FROM dbo.UserInfo );

相關子查詢

USE WJChi; ? SELECT * FROM dbo.UserInfo AS UI WHERE IdentifyId = (SELECT Id FROM dbo.Identify WHERE Id=UI.IdentifyId );

子查詢易錯點

NULL值處理不當

USE WJChi; ? SELECT * FROM dbo.Customers WHERE custid NOT IN(SELECT TOP 10 C.custid FROM dbo.Customers AS C ORDER BY C.custid );

上述查詢語句看起來可以正常運行，但當子查詢的返回結果集中包含NULL值時，上述查詢語句則不會返回任何數據。解釋如下：

20 NOT IN(10, 9, 8, NULL)等價于NOT(20=10 OR 20=9 OR 20=8 OR 20=NULL)，NULL參與的比較預算結果均為Unknown，Unknown參與的或運算結果依然為Unknown。

?? 我們應時刻牢記SQL是三值邏輯，這點很容易引發錯誤

列名處理不當

子查詢中的列名首先從當前查詢中進行解析，若未找到則到外部查詢中查找。子查詢中很有可能無意中包含了外部查詢的列名導致子查詢有自包含子查詢變為相關子查詢而引發邏輯錯誤。

為避免上述錯誤，查詢中的列名盡可能使用完全限定名：[表名].[列名]。

?? 通常我們自己難以發現代碼中的邏輯錯誤，而我們的最終用戶嘗嘗扮演著問題發現者的角色 ?

編寫語義清晰明了的SQL可以很大程度的避免邏輯上的錯誤

表表達式

表表達式，也可稱為表子查詢，是一個命名的查詢表達式，表示一個有效的關系表，因此表表達式必須滿足以下三個條件：

無法表表達式結果集順序

表表達式表示一個關系表，關系型數據庫基于集合理論，表中的數據是無序的。標準SQL中不允許在表表達式中使用ORDER BY子句，除非ORDER BY子句用于展示之外的其他目的，否則會報錯：

除非另外還指定了 TOP、OFFSET 或 FOR XML，否則，ORDER BY 子句在視圖、內聯函數、派生表、子查詢和公用表表達式中無效.

??在查詢表表達式時，除非在外部查詢中指定了ORDER BY子句，否則無法保證查詢結果集中數據的順序。有時候會看到即使外部查詢未使用ORDER BY但查詢結果集按預期順序返回了結果，這是由于數據庫自身優化的結果，依然無法保證每次查詢都能按預期結果返回。

所有列必須顯式指定名稱

所有列名必須唯一

表表達式分為：派生表、公用表表達式、視圖三種類型。其中，派生表與公用表表達式只適用于單語句范圍，即，只存在于當前查詢語句中。視圖則可以被多條查詢語句復用。

派生表

派生表又稱為子查詢表，在外部查詢的FROM子句中進行定義，一旦外部查詢結束，派生表也就不復存在。

在一次查詢中派生表無法被多次引用，若要多次引用，則需要多次書寫派生表：

USE WJChi; ? SELECT Cur.orderyear, Prv.numcusts AS prvnumcusts, Cur.numcusts - Prv.numcusts AS growth FROM (SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcustsFROM dbo.Orders GROUP BY YEAR(orderdate) AS CurLEFT JOIN-- 為了再次使用派生表，需要重復書寫相同邏輯SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcustsFROM dbo.Orders GROUP BY YEAR(orderdate) AS PrvON Cur.orderyear = Prv.orderyear + 1 );

公用表表達式

公用表表達式(CTE)定義方式如下：

WITH...AS (... )

與派生表類似，外部查詢完成后，CTE也就消失了。但，不同于派生表，CTE可以在一次查詢中多次使用（但不能嵌套使用而派生表可以）：

USE WJChi; ? WITH YearlyCount AS (SELECT YEAR(orderdate) AS orderyear, COUNT(DISTINCT custid) AS numcustsFROM dbo.OrdersGROUP BY YEAR(orderdate) ) SELECT Cur.orderyear, Prv.numcusts AS prvnumcusts FROM YearlyCount AS Cur LEFT JOIN -- 再次使用CTE YearlyCount AS Prv ON Cur.orderyear = Prv.orderyear + 1;

這里需要注意一點：CTE之前的SQL語句要以分號（;）結尾。

我們也可以在一次查詢中定義多個CTE：

-- WITH只需要使用一次 WITH Temp1 AS ( ), Temp2 AS ( ) SELECT ...

視圖

視圖是虛擬表，自身不包含數據，只存儲了動態查詢語句，多用于簡化復雜查詢。

視圖創建后被作為數據庫對象而存儲到數據庫中，除非顯式進行刪除。因此，同一個視圖可以被不同的查詢多次使用。

使用以下語句創建視圖：

CREATE VIEW ViewName AS ...

修改視圖：

ALTER VIEW ViewName AS ...

刪除視圖：

DROP VIEW ViewName;

視圖是數據庫中的對象，因此我們可以控制其訪問權限，如：SELECT、UPDATE或訪問視圖底層數據表等。

視圖一旦創建，在底層數據表發生變更后，其不會自動更新。因此，在視圖中使用SELECT語句時盡可能顯式的指定所需列，而不是使用SELECT *。可以使用存儲過程：sp_refreshview和sp_refreshsqlmodule來更新視圖的元數據，或者使用ALTER語句修改視圖定義。

關于是否應該使用視圖，仁者見仁，智者見智：

使用SQL Server視圖的優缺點

為什么mysql中很少見到使用視圖功能？

小結

不要讓數據庫（查詢）變得復雜；

表表達式有助于簡化代碼以提升可讀性與可維護性；

推薦閱讀

T-SQL基礎（二）之關聯查詢

轉載于:https://www.cnblogs.com/Cwj-XFH/p/10012936.html

總結

以上是生活随笔為你收集整理的T-SQL基础（三）之子查询与表表达式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。