您能帮我检查hierarchyid为"/1/1/"的节点是否有(或没有)hierarchyid为"%1/1/X"的子节点吗?
这会返回指定项的所有子项,就像您喜欢的那样:
DECLARE @DemoTable AS TABLE
(
Id int identity(1, 1) primary key,
Hierarchy hierarchyid,
Name varchar(100)
)
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/', 'Universe')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/', 'Milky Way')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/2/', 'Andromeda')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/', 'Solar System')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/1/', 'Mercury')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/2/', 'Venus')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/3/', 'Earth')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/3/1/', 'Moon')
INSERT INTO @DemoTable (Hierarchy, Name) VALUES ('/1/1/4/', 'Mars')
--
-- All children Solar System
--
SELECT
T.Hierarchy.GetLevel() as Level,
T.Hierarchy.ToString() as Hierarchy,
T.Name
FROM @DemoTable T
JOIN @DemoTable P ON
T.Hierarchy.GetAncestor(1) = P.Hierarchy
WHERE P.Name = 'Solar System'
ORDER BY
T.Hierarchy
您可以通过查看hierarchyid在其正下方的级别上是否有任何子级来检查这一点。
DECLARE @hierarchyID hierarchyid
SET @hierarchyID = '/1/1/'
SELECT CASE
WHEN EXISTS (
SELECT 1
FROM HierarchyTable
WHERE HierarchyIDColumn.IsDescendantOf(@hierarchyID) = 1
AND HierarchyIDColumn.GetLevel() = @hierarchyID.GetLevel() + 1
)
THEN 1
ELSE 0
END AS HasImmediateChildren