SQL递归生成父子关系的Fullpath

 

直接上代码：

with TAA AS
(
  SELECT Id,ParentId,Name,CAST(1  AS INT )AS NODELEVEL ,CAST(('X'+Id+'X' )  AS NVARCHAR(500)) AS FULLPATH FROM Basinfo WHERE isnull(ParentId,'')='' AND Enabled=1
  UNION ALL

  SELECT A.Id,A.ParentId,A.Name,CAST((TAA.NODELEVEL+1) AS INT ) AS NODELEVEL,CAST(TAA.FULLPATH+A.Id+'X' AS NVARCHAR(500)) AS FULLPATH FROM Basinfo  A 
  INNER JOIN TAA ON A.PARENTID = TAA.Id
  WHERE A.Enabled=1
)

SELECT * TAA

 

 

 

这里插一句：如果不使用

CAST转换类型，可能会报类型异常