SQL Server CET 通用表表达式 之 精典递归

SQL2005 Common Table Expressions（CET）即通用表表达式。

SQLSERVER CET递归使用案例：

1、普通案例

表结构如下：

 

WITH cet_depart

AS

(

    SELECT depart_id, depart_name FROM dbo.Depart

    WHERE depart_id = '004'    --入口

    UNION ALL    --递归时必要

    SELECT d.depart_id, d.depart_name FROM  depart d INNER JOIN cet_depart c

    ON CAST (c.depart_id AS INT) = d.upper_depart_id  --递归条件

)

 

SELECT * FROM cet_depart

OPTION (MAXRECURSION 20)    --最大递归次数

 

结果集： 

 

2、经典实战

• IF OBJECT_ID('Dept') IS NOT NULL    
•   DROP TABLE Dept     
• go   
•   
• CREATE TABLE Dept (Id INT , ParentId INT , DeptName NVARCHAR(10))   
•   
• INSERT Dept SELECT 1 ,   0 , N'食品'  
•   UNION ALL SELECT 2 ,   1 , N'水果'  
•   UNION ALL SELECT 3 ,   1 , N'蔬菜'  
•   UNION ALL SELECT 4 ,   2 , N'香蕉'  
•   UNION ALL SELECT 5 ,   2 , N'苹果'  
•   UNION ALL SELECT 6 ,   3 , N'青菜'  
•      
•   UNION ALL SELECT 11 ,  0 , N'计算机'  
•   UNION ALL SELECT 12 , 11 , N'软件'  
•   UNION ALL SELECT 13 , 11 , N'硬件'  
•   UNION ALL SELECT 14 , 12 , N'Office'  
•   UNION ALL SELECT 15 , 12 , N'Emeditor'  
•   UNION ALL SELECT 16 , 13 , N'内存'  
•   
• --得到当前及所有子级 --水果类   
• ;WITH cte AS  
• (   
•     SELECT Id,ParentId,DeptName FROM Dept WHERE id=2   
•     UNION ALL  
•     SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.ParentId=b.Id   
• )   
•   
• SELECT * FROM cte   
• 
  
• -- Id          ParentId    DeptName   
• -- ----------- ----------- ----------   
• -- 2           1           水果   
• -- 4           2           香蕉   
• -- 5           2           苹果   
• --    
• -- (3 行受影响)   
•   
• --得到当前及所有父级   
• ;WITH cte AS  
• (   
•     SELECT Id,ParentId,DeptName FROM Dept WHERE id=16   
•     UNION ALL  
•     SELECT a.Id,a.ParentId,a.DeptName FROM Dept a,cte b WHERE a.Id=b.ParentId   
• )   
•   
• SELECT * FROM cte   
•   
• -- Id          ParentId    DeptName   
• -- ----------- ----------- ----------   
• -- 16          13          内存   
• -- 13          11          硬件   
• -- 11          0           计算机   
• --    
• -- (3 行受影响)   
•   
• --得到所有路径   
• ;WITH cte AS  
• (   
•     SELECT Id,ParentId,DeptName,Path=CAST(DeptName AS VARCHAR(MAX)) FROM Dept WHERE parentid=0   
•     UNION ALL  
•     SELECT a.Id,a.ParentId,a.DeptName,CAST(b.Path +'/'+a.DeptName AS VARCHAR(MAX)) FROM Dept a,cte b WHERE a.ParentId=b.Id   
• )   
•   
• SELECT * FROM cte   
•   
• -- Id          ParentId    DeptName   Path   
• -- ----------- ----------- ---------- -------------------------   
• -- 1           0           食品         食品   
• -- 11          0           计算机       计算机   
• -- 12          11          软件         计算机/软件   
• -- 13          11          硬件         计算机/硬件   
• -- 16          13          内存         计算机/硬件/内存   
• -- 14          12          Office       计算机/软件/Office   
• -- 15          12          Emeditor     计算机/软件/Emeditor   
• -- 2           1           水果         食品/水果   
• -- 3           1           蔬菜         食品/蔬菜   
• -- 6           3           青菜         食品/蔬菜/青菜   
• -- 4           2           香蕉         食品/水果/香蕉   
• -- 5           2           苹果         食品/水果/苹果   
• --    
• -- (12 行受影响)