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;
转载于:https://blog.51cto.com/10960988/1791065