在sql server中利用with as實現遞迴功能

標籤：nio   cep   rom   建立表   列表   express   獨立   工作   intersect   

在sqlserver2005之前，要實現遞迴功能比較麻煩，比如可能會要用到暫存資料表與while語句來迴圈。自sqlserver2005之後，新增了with as功能文法，即 公用運算式(CTE),讓遞迴實現起來變的簡單了。

本章我們主要示範如何利用with as功能實現一個簡單的遞迴功能。
在這之前先看一下cte的文法：

[ WITH <common_table_expression > [ ,...n ] ]
<common_table_expression >::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
參數說明：
expression_name:
通用資料表運算式的有效標識符。 expression_name 必須與在同一 WITH <common_table_expression > 子句中定義的任何其他通用資料表運算式的名稱不同，但 expression_name 可以與基表或基視圖的名稱相同。在查詢中對 expression_name 的任何引用都會使用通用資料表運算式，而不使用基底物件。

column_name:
在通用資料表運算式中指定列名。在一個 CTE 定義中不允許出現重複的名稱。指定的列名數必須與CTE_query_definition結果集中列數匹配。只有在查詢定義中為所有結果列都提供了不同的名稱時，列名稱列表才是可選的。

CTE_query_definition:
指定一個其結果集填充通用資料表運算式的 SELECT 語句。除了 CTE 不能定義另一個 CTE 以外，CTE_query_definition的 SELECT 語句必須滿足與建立視圖時相同的要求。
如果定義了多個 CTE_query_definition，則這些查詢定義必須用下列一個集合運算子聯結起來：UNION ALL、UNION、EXCEPT 或 INTERSECT。

--開始執行個體示範--

先建立一個倉庫表，表名為Storage_Depository，該表有三個欄位:DID(倉庫編號),DName(倉庫名稱),PID(父倉庫編號).
通過這樣一個簡單表，就可以將所有倉庫資訊，通過DID與PID欄位來建立一個樹型結構。
建立表的sql語句：

Create table Storage_Depository
(
DID varchar(50) not null primary key,
DName varchar(50) not null,
PID varchar(50) null
)
然後往該表插入示範資料：

insert into Storage_Depository(DID,DName,PID)
select ‘A‘,‘A倉庫‘,null
union all
select ‘A-1‘,‘A-1倉庫‘,‘A‘
union all
select ‘A-2‘,‘A-2倉庫‘,‘A‘
union all
select ‘A-1-1‘,‘A-1-1倉庫‘,‘A-1‘
union all
select ‘B‘,‘B倉庫‘,null
從上面的資料可以看的出來，A的子倉為A-1與A-2倉，而A-1-1為A-1的子倉，B倉是一個獨立的倉庫，與A倉平級。
下面，我們通過with as功能，查出A倉下面的所有子倉：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID=‘A‘
union all
select A.DID,A.DName,A.PID from Storage_Depository A,w_Storage_Depository B where A.PID=B.DID
)
select * from w_Storage_Depository
代碼很簡短，也非常容易讓人理解.

反過來，比如我們要查出A-1-1倉的所有上級倉，稍稍改一下上面的sql語句就可以了：

with w_Storage_Depository as
(
select DID,DName,PID from Storage_Depository where DID=‘A-1-1‘
union all
select A.DID,A.DName,A.PID from Storage_Depository A,
w_Storage_Depository B where A.DID=B.PID
)
select * from w_Storage_Depository
很簡單吧，熟練使用CTE後，會發現它會給我們的工作帶來很大的便利.

 

在sql server中利用with as實現遞迴功能