Hierarchyid (hierarchy) data type

Instance table structure

CREATE TABLE [dbo]. [EMP] ([ID] [int] IDENTITY (+), [Name] [varchar] (+), [ORG] [HierarchyID],) INSERT into tempdb. EMP (name,org) VALUES (' Wu xx ', '/') INSERT into tempdb. EMP (name,org) VALUES (' Xie xx ', '/1/') INSERT into tempdb. EMP (name,org) VALUES (' Zhao xx ', '/2/') INSERT into tempdb. EMP (name,org) VALUES (' Lou xx ', '/1/1/') INSERT into tempdb. EMP (name,org) VALUES (' Square xx ', '/1/2/') INSERT into tempdb. EMP (name,org) VALUES (' Zhan xx ', '/1/3/') INSERT into tempdb. EMP (name,org) VALUES (' Shen xx ', '/2/1/')

Instance base data

SELECT *,org.tostring (), Org.getlevel () from tempdb. Emp

　　

HierarchyID Method Reference

ToString: Node path

--node. ToString () SELECT *,org.tostring () from tempdb: Emp

　　

Getlevel: Node Depth

--node. Getlevel () SELECT *,org.getlevel () from tempdb. Empselect *,org.getlevel () from tempdb. Empwhere org.getlevel () = 1

　　

GetAncestor: node n-level ancestor, that is, the child node of the specified level is reversed by the ancestor node.

--child. GetAncestor (N)--A Level two child node under the specified node select @CurrentNode = Org from tempdb. EMP WHERE Name = ' Wu xx ' SELECT * from tempdb. EMP WHERE org.getancestor (2) = @CurrentNode

Getdescendant: node nodes, greater than child1, and less than child2,child1/child2 can be null.

--parent. Getdescendant (CHILD1,CHILD2)-Added Wu Zi node, with Xie/playwriting level, but greater than Xie and less than Zhao. DECLARE @Child1 hierarchyiddeclare @Child2 hierarchyidselect @CurrentNode = Org from tempdb. EMP WHERE Name = ' Wu xx ' SELECT @Child1 = Org from tempdb. EMP WHERE Name = ' Xie xx ' SELECT @Child2 = Org from tempdb. EMP WHERE Name = ' Zhao xx ' INSERT into tempdb. EMP (name,org) VALUES (' in xx ', @CurrentNode. Getdescendant (@Child1, @Child2))

Isdescendantof: Whether it is a descendant node of a node

--child. Isdescendantof (parent)--the entire descendant node under the specified node, containing itself select @CurrentNode = Org from tempdb. EMP WHERE Name = ' Wu xx ' SELECT * from tempdb. EMP WHERE org.isdescendantof (@CurrentNode) = 1

Getreparentedvalue: The current node changes the new path, from Oldroot to Newroot

--node. Getreparentedvalue (Oldroot,newroot)--from point to Wu Zi node, update point to Jason node. DECLARE @OldRoot hierarchyiddeclare @NewRoot hierarchyidselect @CurrentNode = Org from tempdb. EMP WHERE Name = ' for xx ' SELECT @OldRoot = Org from tempdb. EMP WHERE Name = ' Wu xx ' SELECT @NewRoot = Org from tempdb. EMP WHERE Name = ' Zhao xx ' UPDATE tempdb: EMP SET [email protected] (@OldRoot, @NewRoot) WHERE Org = @CurrentNode

　　

Getroot: Root node

--hierarchyid::getroot () SELECT *from tempdb. Empwhere Org = Hierarchyid::getroot ()

Parse: Converts from a path string to a HierarchyID value, which is re-encoded.

--hierarchyid::P arse () DECLARE @StringNode varchar (SET @StringNode = '/1/1/' SELECT *,org.tostring () from tempdb. EMP WHERE Org = HierarchyID::P arse (@StringNode)

Hierarchyid (hierarchy) data type