T-SQL HIERACHYID.GETREPARENTEDVALUE

来源：互联网发布：js中的top属性编辑：程序博客网时间：2024/05/19 12:23

 SELECT  NodeId,  NodeId.ToString() AS NodeIdPath,  dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPathFROM  familyORDER BY  NodeLevel, NodeId

NodeId NodeIdPath NodeIdDisplayPath0x / 爷爷0x58 /1/ 爷爷 -> 父亲0x68 /2/ 爷爷 -> 叔叔0x5AC0 /1/1/ 爷爷 -> 父亲 -> 长子0x5B40 /1/2/ 爷爷 -> 父亲 -> 次子0x5AD6 /1/1/1/ 爷爷 -> 父亲 -> 长子 -> 长孙0x5B56 /1/2/1/ 爷爷 -> 父亲 -> 次子 -> 长女0x5B5A /1/2/2/ 爷爷 -> 父亲 -> 次子 -> 次孙

由于叔叔没有子嗣,次子被过继给他.

DECLARE @FamilyToMove hierarchyidDECLARE @OldParent hierarchyidDECLARE @NewParent hierarchyidSELECT @FamilyToMove = NodeId FROM  family WHERE familyId = 113 -- 次子SELECT @OldParent = NodeId FROM  family WHERE familyId = 11 -- 父亲SELECT @NewParent = NodeId FROM  family WHERE familyId = 13 -- 叔叔-- 次子过继给叔叔UPDATE family SET   NodeId = @FamilyToMove.GetReparentedValue(@OldParent, @NewParent) WHERE NodeId = @FamilyToMove SELECT  NodeId,  NodeId.ToString() AS NodeIdPath,  dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPathFROM  familyORDER BY  NodeLevel, NodeId

NodeId NodeIdPath NodeIdDisplayPath0x / 爷爷0x58 /1/ 爷爷 -> 父亲0x68 /2/ 爷爷 -> 叔叔0x5AC0 /1/1/ 爷爷 -> 父亲 -> 长子0x6B40 /2/2/ 爷爷 -> 叔叔 -> 次子0x5AD6 /1/1/1/ 爷爷 -> 父亲 -> 长子 -> 长孙0x5B56 /1/2/1/ NULL0x5B5A /1/2/2/ NULL

结果显示,叔叔过继了次子, 但是, 他的孩子长女,次孙却成了孤儿. ORPHANT NODES.

呵呵, 显然不成功.

这里需要移动整个SUBTREE.

ROLLBACK TRANSCACTION.

DECLARE @FamilyToMove hierarchyidDECLARE @OldParent hierarchyidDECLARE @NewParent hierarchyidSELECT @FamilyToMove = NodeId FROM  family WHERE familyId = 113 -- 次子SELECT @OldParent = NodeId FROM  family WHERE familyId = 11 -- 父亲SELECT @NewParent = NodeId FROM  family WHERE familyId = 13 -- 叔叔-- 次子过继给叔叔UPDATE family SET   NodeId = nodeid.GetReparentedValue(@OldParent, @NewParent) WHERE NodeId.IsDescendantOf(@FamilyToMove) = 1  -- 注意:ISDESCENDANTOF包括次子本人        SELECT  NodeId,  NodeId.ToString() AS NodeIdPath,  dbo.fnGetFullDisplayPath(NodeId) AS NodeIdDisplayPathFROM  familyORDER BY  NodeLevel, NodeId

NodeId NodeIdPath NodeIdDisplayPath0x / 爷爷0x58 /1/ 爷爷 -> 父亲0x68 /2/ 爷爷 -> 叔叔0x5AC0 /1/1/ 爷爷 -> 父亲 -> 长子0x6B40 /2/2/ 爷爷 -> 叔叔 -> 次子0x5AD6 /1/1/1/ 爷爷 -> 父亲 -> 长子 -> 长孙0x6B56 /2/2/1/ 爷爷 -> 叔叔 -> 次子 -> 次孙0x6B5A /2/2/2/ 爷爷 -> 叔叔 -> 次子 -> 长孙女

这次,成了.