SQL Server 树查询

最新推荐文章于 2023-03-15 13:07:51 发布

转载最新推荐文章于 2023-03-15 13:07:51 发布 · 162 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/mingjing/p/6041523.html

文章标签：

#数据库

本文介绍了一个使用SQLServer进行树状结构查询的方法。通过递归公用表表达式(CTE)来构建多层次的数据关系，实现组织架构等树形结构数据的查询。此查询能够清晰展示从顶级节点到子节点的层级关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL Server 树查询

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;

---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4
------------------Test Lead 2
---------------------Tester 3
---------------------Tester 4
---------------------Tester 5