在sql server中利用with as实现递归功能

darkpoon

在sql server中利用with as实现递归功能
 
在sqlserver2005之前，要实现递归功能比较麻烦，比如可能会要用到临时表与while语句来循环。自sqlserver2005之后，新增了with as功能语法，即 公用表达式(CTE),让递归实现起来变的简单了。 
 
本章我们主要演示如何利用with as功能实现一个简单的递归功能。 
 
--开始实例演示-- 
 
先创建一个仓库表，表名为Storage_Depository，该表有三个字段:DID(仓库编号),DName(仓库名称),PID(父仓库编号). 
通过这样一个简单表，就可以将所有仓库信息，通过DID与PID字段来创建一个树型结构。 
创建表的sql语句： 
Sqlserver代码  
Create table Storage_Depository  
(  
DID varchar(50) not null primary key,  
DName varchar(50) not null,  
PID varchar(50) null  
)  
 
然后往该表插入演示数据： 
 
Sqlserver代码  
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仓下面的所有子仓： 
 
Sqlserver代码  
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语句就可以了： 
 
Sqlserver代码  
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