图例为树状结构,想了解F1是否存在于B1的下级节点中(答案是存在),D3是否存在于B1节点中(答案是不存在)。
A1
/ \
B1 B2
/ \ \
C1 C2 C3
/ \ / /
D1 D2 D3
\ \ /
E1 E2
\ /
F1
可以使用存储过程或者函数解决等方法
------解决思路----------------------
DECLARE @startNode CHAR(2) = 'B1'
DECLARE @checkNode CHAR(2) = 'D3'
;WITH DATA(parentid, ID) AS
(SELECT NULL, 'A1' UNION ALL
SELECT 'A1', 'B1' UNION ALL
SELECT 'A1', 'B2' UNION ALL
SELECT 'B1', 'C1' UNION ALL
SELECT 'B1', 'C2' UNION ALL
SELECT 'C1', 'D1' UNION ALL
SELECT 'C1', 'D2' UNION ALL
SELECT 'D1', 'E1' UNION ALL
SELECT 'E1', 'F1' UNION ALL
SELECT 'C2', 'D2' UNION ALL
SELECT 'D2', 'E2' UNION ALL
SELECT 'E2', 'F1' UNION ALL
SELECT 'B2', 'C3' UNION ALL
SELECT 'C3', 'D3' UNION ALL
SELECT 'D3', 'E2'
)
, datatemp AS
(
SELECT * FROM DATA WHERE ID = @startNode
UNION ALL
SELECT data.* FROM datatemp
INNER JOIN DATA ON data.parentid = datatemp.id
--where data.ID <> datatemp.id
)
SELECT CASE WHEN (SELECT COUNT(*) FROM datatemp WHERE id = @checkNode) > 1 THEN 'true' ELSE 'false' END