Common hierarchyid operations

?

?

--------- Built-in function ------------

Select hierarchyid: getroot () -- 0x

Select hierarchyid: parse ('/1/1/') -- 0x5ac0

Select cast (0x5ac0 as hierarchyid) -- 0x5ac0

Select cast ('/1/' As hierarchyid) -- 0x5ac0

Select cast (0x5ac0 as hierarchyid). tostring () --/1/1/

Select cast (0x5ac0 as hierarchyid). getlevel () -- 2

?

----------- Isdescendantof ------------ determine whether @ node is a subnode of @ parent

Declare @ node hierarchyid

Declare @ parent hierarchyid

Set @ node = '/1/2/3/4 /'

Set @ parent = '/1/2 /'

?

Select @ node. isdescendantof (@ parent) -- 1

Select @ parent. isdescendantof (@ node) -- 0

?

-------- Getancestor (n) ---------- return the ancestor of the specified level.

Declare @ hy hierarchyid

Declare @ C int

Set @ hy = '/1/1/2/1 /'

Set @ [email protected] ()

Select @ Hy. getancestor (0). tostring () --/1/1/2/1/

Select @ Hy. getancestor (1). tostring () --/1/1/2/

Select @ Hy. getancestor (@ C). tostring ()--/

Select @ Hy. getancestor (@ C + 1). tostring () -- null

?

?

?

----------- Getdescendant ----------- returns a subset.

// 1. If the parent level is null, null is returned.

?

--- If the parent level is not null ----

-- 2. If Child1 and child2 are null, the parent child is returned. --

Declare @ hy hierarchyid

Set @ hy = '/1/1 /'

Select @ Hy. getdescendant (null, null). tostring () --/1/1/1/

?

-- 3. The returned value is between Child1 and child2. Child1> child2 must be a subset of @ hy --

Select @ Hy. getdescendant ('/1/1/5/', null). tostring () --/1/1/6/

Select @ Hy. getdescendant (null, '/1/1/5/'). tostring () --/1/1/4/

Select @ Hy. getdescendant ('/1/1/2/', '/1/1/5/'). tostring () --/1/1/3/

Select @ Hy. getdescendant ('/1/1/3/', '/1/1/4/'). tostring () --/1/1/3.1/

Select @ Hy. getdescendant (null, '/1/1/1/5/'). tostring () -- an exception is reported.

Select @ Hy. getdescendant ('/1/1/5/', '/1/1/3/'). tostring () -- an exception is reported.

?

--------- ◆ Getreparentedvalue: can be used to move nodes --------------

Note: @ parent is the node's ancestor

?

Declare @ node hierarchyid

Declare @ nodechild1 hierarchyid

Declare @ parent hierarchyid, @ new hierarchyid

Set @ node = '/1/2/3/4 /'

Set @ nodechild1 = '/1/2/3/4/5 /'

Set @ parent = '/1/2 /'

Set @ new = '/ 5/6/7 /'

?

Set @ [email protected] (@ parent, @ new)

Select @ node. tostring () --/5/6/7/3/4/

Select @ nodechild1.getreparentedvalue (hierarchyid: parse ('/1/2/3/4/'), @ node). tostring ()

?

?

Move subtree

Another common operation is to move the subtree. Which of the following processes is used?@ Oldmgr? As a parameter to make it (including?@ Oldmgr) Become?@ Newmgr? .

Create procedure moveorg (@ oldmgr nvarchar (256), @ newmgr nvarchar (256 ))

As

Begin

Declare @ nold hierarchyid, @ nNew hierarchyid

Select @ nold = orgnode from HumanResources. employeedemo where loginid = @ oldmgr;

?

SET transaction isolation level serializable

Begin transaction

Select @ nNew = orgnode from HumanResources. employeedemo where loginid = @ newmgr;

?

Select @ nNew = @ nNew. getdescendant (max (orgnode), null)

From HumanResources. employeedemo where orgnode. getancestor (1) [email protected];

?

Update HumanResources. employeedemo

Set orgnode = orgnode. getreparentedvalue (@ nold, @ nNew)

Where orgnode. isdescendantof (@ nold) = 1;

?

Commit transaction

End;

Go

?

?

?

Refer:

?

Http://technet.microsoft.com/zh-cn/library/bb677212 (V = SQL .105). aspx

Http://blog.csdn.net/tjvictor/article/details/4395681

Http://msdn.microsoft.com/zh-cn/library/bb677290 (V = SQL .105). aspx

?

?

?

????

Common hierarchyid operations