直接上代码:
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转换类型,可能会报类型异常