WITH tab
AS ( SELECT id , pid ,Cast([name] as nvarchar(4000)) tName , 1 AS [Level]
FROM [dbo].[ERP_PartNo_Type] WITH ( NOLOCK ) --表名
WHERE id = 11
UNION ALL
SELECT b.id , b.pid ,b.[name]+'|'+a.tName tName , a.[Level] + 1
FROM tab a ,
[ERP_PartNo_Type] b WITH ( NOLOCK )
WHERE a.pid = b.id
)
SELECT * FROM tab WITH ( NOLOCK ) ORDER BY [level] DESC
CTE定位点类型和递归部分的类型不匹配
在CTE递归测试,也就是部门名称拼接的时候,遇到了问题:
修改为下面的正常
CTE递归时,运算要求同结构,同类型; 而运算后的默认类型和自定义的类型不一致, 显示转换下就OK了, 选择转换方式要看具体需求。