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 行受影响)