--查询所有下级

DECLARE @BOSS hierarchyid

SELECT @BOSS=orgNode FROM subjectClass WHERE ID=1011

SELECT *,orgNode.ToString() as org FROM subjectClass WHERE orgNode.IsDescendantOf(@BOSS)=1 and valid=1

--查询所有上级

DECLARE @Employee hierarchyid

SELECT @Employee=orgNode FROM subjectClass WHERE ID=11749

SELECT *,orgNode.GetLevel()AS Level FROM subjectClass WHERE @Employee.IsDescendantOf(orgNode)=1 and valid=1

--查询下级

DECLARE @node HIERARCHYID

SELECT @node=node FROM sys_area WHERE code=''

SELECT *,node.ToString()[nodet] FROM sys_area WHERE node.IsDescendantOf(@node)=1

GO

DECLARE @node HIERARCHYID=HIERARCHYID::Parse('/24/')

SELECT *,node.ToString()[nodet] FROM sys_area WHERE node.IsDescendantOf(@node)=1