MSSQL 获取递归树形表数据

本文主要是介绍MSSQL 获取递归树形表数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1.根据节点主键，查询当前节点和所有子节点

WITH SOURCE AS (
  SELECT * FROM TableName WHERE DeleteState = 0
),
LIST AS (
  SELECT value FROM STRING_SPLIT('2111011043240001', ',')
),
RESULT AS (
  SELECT SOURCE.* FROM SOURCE 
  INNER JOIN LIST ON SOURCE.Code = LIST.value
  UNION ALL
  SELECT SOURCE.* FROM RESULT 
    INNER JOIN SOURCE ON RESULT.Code = SOURCE.ParentCode
)

SELECT Code, ParentCode, NodeName, NodeIndex, IsLastNode FROM RESULT

2.根据节点主键，查询当前节点和所有父节点

WITH SOURCE AS (
  SELECT * FROM TableName WHERE DeleteState = 0
),
LIST AS (
  SELECT value FROM STRING_SPLIT('2111011043260028', ',')
),
RESULT AS (
  SELECT SOURCE.* FROM SOURCE 
  INNER JOIN LIST ON SOURCE.Code = LIST.value
  UNION ALL
  SELECT SOURCE.* FROM RESULT 
    INNER JOIN SOURCE ON RESULT.ParentCode = SOURCE.Code
)
SELECT Code, ParentCode, NodeName, NodeIndex, IsLastNode FROM RESULT

这篇关于MSSQL 获取递归树形表数据的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！