对 SQL 层次结构数据类型中的节点重新排序

发布于 2024-08-02 04:38:00 字数 310 浏览 5 评论 0原文

在 SQL 2008 上使用层次结构数据类型。层次结构中的节点如下所示：

value   node 
36    /8/1/
38    /8/2/
34    /8/3/
40    /8/4/
42    /8/5/
44    /8/6/
46    /8/7/
48    /8/8/

我想重新排列节点，以便 /8/3/ 和 /8/1/ 交换位置。关于如何做到这一点有什么想法吗？

到目前为止，我唯一的想法是，我将所有节点加载到数组中的某个级别上，按照我想要的方式对它们进行排序，从表中删除它们并以排序的形式插入。

原文

Using Hierarchy data type on SQL 2008. Nodes in my hierarchy go like this:

value   node 
36    /8/1/
38    /8/2/
34    /8/3/
40    /8/4/
42    /8/5/
44    /8/6/
46    /8/7/
48    /8/8/

I'd like to rearrange nodes so that /8/3/ and /8/1/ switch places. Any idea on how to do this?

Only idea that I have so far is that I load all nodes on a level in Array, order them the way I want them to be, delete them from table and insert in sorted form.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往昔成烟 2024-08-09 04:38:00

如果（就像在您的示例中）您提前知道要操作的hierarchyid值，您可以直接执行此操作，例如：

-- Place 1st node between 2nd and 3rd
UPDATE yourTable SET node = CAST('/8/2.5/' AS hierarchyid) WHERE value = 36;
-- Move 3rd node to 1st
UPDATE yourTable SET node = CAST('/8/1/' AS hierarchyid) WHERE value = 34;

如果您需要动态获取新的hierarchyid值，请查看GetDescendant() 和 GetAncestor() 函数。使用它，该示例将如下所示：

DECLARE @Parent hierarchyid, @Child1 hierarchyid, @Child2 hierarchyid

-- Grab hierarchyids from 2nd and 3rd node
SELECT @Child1 = node FROM yourTable WHERE value = 38;
SELECT @Child2 = node FROM yourTable WHERE value = 34;
-- Get the parent hierarchyid
SELECT @Parent = @Child1.GetAncestor(1);

-- Update 1st node to end up between 2nd and 3rd
UPDATE yourTable SET node = @Parent.GetDescendant(@Child1, @Child2) WHERE value = 36;
-- Update 3rd node to end up before 2nd
UPDATE yourTable SET node = @Parent.GetDescendant(NULL, @Child1) WHERE value = 34;

请注意，在此示例中，hierarchyid 不会保持相同。至少有一个最终会使用“分数”来表示其位置（例如“/8/2.5/”而不是“/8/3/”）。

If (like in your example) you know the hierarchyid values you want to manipulate in advance, you can do it directly, e.g.:

-- Place 1st node between 2nd and 3rd
UPDATE yourTable SET node = CAST('/8/2.5/' AS hierarchyid) WHERE value = 36;
-- Move 3rd node to 1st
UPDATE yourTable SET node = CAST('/8/1/' AS hierarchyid) WHERE value = 34;

If you need to get your new hierarchyid values dynamically, take a look into the GetDescendant() and GetAncestor() functions. Using that, the example would look something like this:

DECLARE @Parent hierarchyid, @Child1 hierarchyid, @Child2 hierarchyid

-- Grab hierarchyids from 2nd and 3rd node
SELECT @Child1 = node FROM yourTable WHERE value = 38;
SELECT @Child2 = node FROM yourTable WHERE value = 34;
-- Get the parent hierarchyid
SELECT @Parent = @Child1.GetAncestor(1);

-- Update 1st node to end up between 2nd and 3rd
UPDATE yourTable SET node = @Parent.GetDescendant(@Child1, @Child2) WHERE value = 36;
-- Update 3rd node to end up before 2nd
UPDATE yourTable SET node = @Parent.GetDescendant(NULL, @Child1) WHERE value = 34;

Note that the hierarchyids do not stay the same in this example. At least one will end up using a 'fraction' for its position (e.g. '/8/2.5/' instead of '/8/3/').

回复收藏 0 原文