久久青青视频,亚洲成人二区,亚洲国产免费

本文為原創，如需轉載，請注明作者和出處，謝謝！

上一篇： SQL Server2005雜談（1）：使用公用表表達式（CTE）簡化嵌套SQL

先看如下一個數據表（t_tree）：
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <meta name="ProgId" content="Word.Document"> <meta name="Generator" content="Microsoft Word 11"> <meta name="Originator" content="Microsoft Word 11"> <link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"> <style> <!--{cps..2}</style>

上圖顯示了一個表中的數據，這個表有三個字段： id 、 node_name 、 parent_id 。實際上，這個表中保存了一個樹型結構，分三層：省、市、區。其中 id 表示當前省、市或區的 id 號、 node_name 表示名稱、 parent_id 表示節點的父節點的 id 。
現在有一個需求，要查詢出某個省下面的所有市和區（查詢結果包含省）。如果只使用 SQL 語句來實現，需要使用到游標、臨時表等技術。但在 SQL Server2005 中還可以使用 CTE 來實現。

從這個需求來看屬于遞歸調用，也就是說先查出滿足調價的省的記錄，在本例子中的要查“遼寧省”的記錄，如下：

id node_name parent_id

1 遼寧省 0

然后再查所有 parent_id 字段值為 1 的記錄，如下：

id node_name parent_id

2 沈陽市 1

3 大連市 1

最后再查 parent_id 字段值為 2 或 3 的記錄，如下：

id node_name parent_id

4 大東區 2

5 沈河區 2

6 鐵西區 2

將上面三個結果集合并起來就是最終結果集。

上述的查詢過程也可以按遞歸的過程進行理解，即先查指定的省的記錄（遼寧省），得到這條記錄后，就有了相應的 id 值，然后就進入了的遞歸過程，如下圖所示。

從上面可以看出，遞歸的過程就是使用 union all 合并查詢結果集的過程，也就是相當于下面的遞歸公式：

resultset(n) = resultset(n-1)union allcurrent_resultset

其中 resultset(n) 表示最終的結果集， resultset(n - 1) 表示倒數第二個結果集， current_resultset 表示當前查出來的結果集，而最開始查詢出“遼寧省”的記錄集相當于遞歸的初始條件。而遞歸的結束條件是 current_resultset 為空。下面是這個遞歸過程的偽代碼：

public resultsetgetResultSet(resultset)
{
if (resultsetis null )
{
current_resultset = 第一個結果集（包含省的記錄集）
將結果集的id保存在集合中
getResultSet(current_resultset)
}
current_resultset = 根據id集合中的id值查出當前結果集
if (current_resultis null ) return resultset
將當前結果集的id保存在集合中
return getResultSet(resultsetunionallcurrent_resultset)
}

// 獲得最終結果集
resultset = getResultSet( null )

從上面的過程可以看出，這一遞歸過程實現起來比較復雜，然而 CTE 為我們提供了簡單的語法來簡化這一過程。
實現遞歸的 CTE 語法如下：

[ WITH<common_table_expression>[,n ] ]
< common_table_expression > :: =
expression_name [ (column_name[,n ] )]
AS (
CTE_query_definition1 -- 定位點成員（也就是初始值或第一個結果集）
union all
CTE_query_definition2 -- 遞歸成員
)

with
district as
(
-- 獲得第一個結果集，并更新最終結果集
select * from t_tree where node_name = N ' 遼寧省 '
union all
-- 下面的select語句首先會根據從上一個查詢結果集中獲得的id值來查詢parent_id
-- 字段的值，然后district就會變當前的查詢結果集，并繼續執行下面的select語句
-- 如果結果集不為null，則與最終的查詢結果合并，同時用合并的結果更新最終的查
-- 詢結果；否則停止執行。最后district的結果集就是最終結果集。
select a. * from t_treea,districtb
where a.parent_id = b.id
)
select * from district

with
district as
(
select * from t_tree where node_name = N ' 遼寧省 '
union all
select a. * from t_treea,districtb
where a.parent_id = b.id
),
district1 as
(
select a. * from districta where a.id in ( select parent_id from district)
)
select * from district1

注：只有“遼寧省”和“沈陽市”有下子節點。

在定義和使用遞歸 CTE 時應注意如下幾點：

1. 遞歸 CTE 定義至少必須包含兩個 CTE 查詢定義，一個定位點成員和一個遞歸成員。可以定義多個定位點成員和遞歸成員；但必須將所有定位點成員查詢定義置于第一個遞歸成員定義之前。所有 CTE 查詢定義都是定位點成員，但它們引用 CTE 本身時除外。
2. 定位點成員必須與以下集合運算符之一結合使用： UNION ALL 、 UNION 、 INTERSECT 或 EXCEPT 。在最后一個定位點成員和第一個遞歸成員之間，以及組合多個遞歸成員時，只能使用 UNION ALL 集合運算符。
3. 定位點成員和遞歸成員中的列數必須一致。
4. 遞歸成員中列的數據類型必須與定位點成員中相應列的數據類型一致。
5. 遞歸成員的 FROM 子句只能引用一次 CTE expression_name 。
6. 在遞歸成員的 CTE_query_definition 中不允許出現下列項：

（ 1 ） SELECT DISTINCT

（ 2 ） GROUP BY

（ 3 ） HAVING

（ 4 ）標量聚合

（ 5 ） TOP

（ 6 ） LEFT 、 RIGHT 、 OUTER JOIN （允許出現 INNER JOIN ）

（ 7 ）子查詢

（ 8 ）應用于對 CTE_query_definition 中的 CTE 的遞歸引用的提示。

7. 無論參與的 SELECT 語句返回的列的為空性如何，遞歸 CTE 返回的全部列都可以為空。
8. 如果遞歸 CTE 組合不正確，可能會導致無限循環。例如，如果遞歸成員查詢定義對父列和子列返回相同的值，則會造成無限循環。可以使用 MAXRECURSION 提示以及在 INSERT 、 UPDATE 、 DELETE 或 SELECT 語句的 OPTION 子句中的一個 0 到 32,767 之間的值，來限制特定語句所允許的遞歸級數，以防止出現無限循環。這樣就能夠在解決產生循環的代碼問題之前控制語句的執行。服務器范圍內的默認值是 100 。如果指定 0 ，則沒有限制。每一個語句只能指定一個 MAXRECURSION 值。
9. 不能使用包含遞歸公用表表達式的視圖來更新數據。
10. 可以使用 CTE 在查詢上定義游標。遞歸 CTE 只允許使用快速只進游標和靜態（快照）游標。如果在遞歸 CTE 中指定了其他游標類型，則該類型將轉換為靜態游標類型。
11. 可以在 CTE 中引用遠程服務器中的表。如果在 CTE 的遞歸成員中引用了遠程服務器，那么將為每個遠程表創建一個假脫機，這樣就可以在本地反復訪問這些表。

下一篇： SQL Server2005雜談（3）：四個排名函數（row_number、rank、dense_rank和ntile）的比較

國內最棒的Google Android技術社區（eoeandroid），歡迎訪問！

《銀河系列原創教程》發布

《Java Web開發速學寶典》出版，歡迎定購

SQL Server2005雜談（2）：公用表表達式（CTE）的遞歸調用

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義