本文為原創,如需轉載,請注明作者和出處,謝謝!
上一篇:
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"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!--{cps..2}</style>
<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"> <!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style> <!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
上圖顯示了一個表中的數據,這個表有三個字段:
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
值,然后就進入了的遞歸過程,如下圖所示。
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
從上面可以看出,遞歸的過程就是使用
union all
合并查詢結果集的過程,也就是相當于下面的遞歸公式:
resultset(n) = resultset(n-1)union allcurrent_resultset
其中
resultset(n)
表示最終的結果集,
resultset(n - 1)
表示倒數第二個結果集,
current_resultset
表示當前查出來的結果集,而最開始查詢出“遼寧省”的記錄集相當于遞歸的初始條件。而遞歸的結束條件是
current_resultset
為空。下面是這個遞歸過程的偽代碼:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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
語法如下:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
[
WITH<common_table_expression>[,n
]
]
<
common_table_expression
>
::
=
expression_name
[
(column_name[,n
]
)]
AS
(
CTE_query_definition1
--
定位點成員(也就是初始值或第一個結果集)
union
all
CTE_query_definition2
--
遞歸成員
)
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
下面是使用遞歸
CTE
來獲得“遼寧省”及下面所有市、區的信息的
SQL
語句:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
查詢后的結果如下圖所示。
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
下面的
CTE
查詢了非葉子節點:
<!--<br /> <br /> Code highlighting produced by Actipro CodeHighlighter (freeware)<br /> http://www.CodeHighlighter.com/<br /> <br /> -->
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
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
查詢結果如下圖所示。
<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"><!--[if gte mso 9]><xml> Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 </xml><![endif]--><!--[if gte mso 9]><![endif]--><style> <!-- /* Font Definitions */ @font-face {font-family:宋體; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:""@宋體"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Times New Roman";} /* Page Definitions */ @page {} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable { mso-style-parent:""; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} </style> <![endif]-->
注:只有“遼寧省”和“沈陽市”有下子節點。
在定義和使用遞歸
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)的遞歸調用