Sql Server 树形查询

ycvodzf

WITH tree AS (　　SELECT
　　ParentAssetID,
　　AssetID,
　　1 AS x2level,
　　nodename,
　　CAST(nodename AS NVARCHAR(max)) x2name,
　　CAST(+ AssetID AS NVARCHAR(max)) x2id
　　FROM
　　dbo.Assets
　　WHERE
　　ParentAssetID IS NULL
　　UNION ALL
　　SELECT
　　c.ParentAssetID,
　　c.AssetID,
　　tree.x2level + 1,
　　c.nodename,
　　CAST(
　　REPLICATE ('-', x2level * 4) + c.nodename AS NVARCHAR (max)
　　) x2name,
　　tree.x2id + ':|:' + CAST(c.AssetID AS NVARCHAR(max)) x2id
　　FROM
　　dbo.Assets c
　　INNER JOIN tree ON c.ParentAssetID = tree.AssetID
　　
) SELECT
　　x2name,
　　AssetID,
　　ParentAssetID
　　
FROM
　　tree
　　
ORDER BY
　　x2id;