SQL递归公用表表达式（CTE）的使用

递归查询示例

Common Table Expressions(CTE)

注意事项

在递归公用表表达式的递归部分不允许使用外部联接。

表数据

RowID	ID	Name	PID
1	10001	总经理室	NULL
2	10002	副总经理室	10001
3	20001	研发部	10002
4	20002	采购部	10002
5	20003	人事部	10002

父节点查子节点

/**
查总经理室(10001)所有下级部门
**/
WITH DeptCTE AS (
	SELECT ID,Name,CAST('总经理室'+'->'+Name AS NVARCHAR(MAX)) AS DPATH FROM Dept WHERE PID='10001'
	UNION ALL
	SELECT b.ID,b.Name,a.DPATH+'->'+b.Name FROM DeptCTE a JOIN Dept b ON a.ID=b.PID
)
SELECT * FROM DeptCTE;

ID	Name	DPATH
10002	副总经理室	总经理室->副总经理室
20001	研发部	总经理室->副总经理室->研发部
20002	采购部	总经理室->副总经理室->采购部
20003	人事部	总经理室->副总经理室->人事部

子节点查父节点

/**
查研发部(20001)所有上级部门
**/
WITH DeptCTE2 AS (
	SELECT ID,Name,PID,CAST(Name AS NVARCHAR(MAX)) AS DPATH FROM Dept WHERE ID='20001'
	UNION ALL
	SELECT b.ID,b.Name,b.PID,a.DPATH+'->'+b.Name FROM DeptCTE2 a JOIN Dept b ON a.PID=b.ID
)
SELECT * FROM DeptCTE2 WHERE ID<>'20001';

ID	Name	PID	DPATH
10002	副总经理室	10001	研发部->副总经理室
10001	总经理室	NULL	研发部->副总经理室->总经理室

更多使用场景

1. 合并列值