sql server 递归查询

最新推荐文章于 2023-01-07 08:30:57 发布

努力的草根

最新推荐文章于 2023-01-07 08:30:57 发布

阅读量814

点赞数

分类专栏： SQL Server 文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/qq61394323/article/details/121745141

版权

SQL Server 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

该博客探讨了如何使用SQL的递归查询来追溯数据间的层级关系。通过WITH子句创建的CTE（公共表表达式），展示了从根节点5339693向下递归获取所有子项，以及从特定ID9451204向上回溯到父级的完整路径。内容涉及数据库查询优化和数据结构理解。

摘要由CSDN通过智能技术生成

WITH mytest(ID,SerialNumber,ParentID)
AS
(
	SELECT ID,SerialNumber,ParentID FROM dbo.mesMaterialUnit
	WHERE ID = 5339693
	UNION ALL
	SELECT a.ID,a.SerialNumber,a.ParentID FROM dbo.mesMaterialUnit a
	INNER JOIN mytest ON a.ParentID = mytest.ID
	WHERE a.PartID = 24
)
SELECT * FROM mytest

向下递归

ID SerialNumber ParentID
5339693   0000703858600001   NULL
5339694   0000703858600001131800001   5339693
5339695   0000703858600001131800002   5339693
5339697   0000703858600001131800003   5339693
5339698   0000703858600001131800004   5339693
5341612   0000703858600001141500003   5339693
5341613   0000703858600001141500004   5339693
5347231   0000703858600001151200001   5339693
5348635   0000703858600001151400001   5339693
5349736   0000703858600001151500001   5339693
5350737   0000703858600001151700001   5339693
5351448   0000703858600001151800001   5339693
5352740   0000703858600001151900001   5339693
5352741   0000703858600001152000001   5339693
9451203   abcd   5339694
9451204   abcd   9451203

WITH test2(ID,SerialNumber,ParentID)
AS(
	SELECT ID,SerialNumber,ParentID FROM dbo.mesMaterialUnit
	WHERE ID = 9451204
	UNION ALL
	SELECT a.ID,a.SerialNumber,a.ParentID FROM dbo.mesMaterialUnit a
	INNER JOIN test2 ON a.ID = test2.ParentID
	WHERE a.PartID = 24
)
SELECT * FROM test2

向上递归

ID               SerialNumber   ParentID
9451204   abcd   9451203
9451203   abcd   5339694
5339694   0000703858600001131800001   5339693
5339693   0000703858600001   NULL