SQL - 循环 - 复制类别

Question

假设类别表有categoryId、name 和parentCategoryId 列，其中categoryId 是标识字段。

现在，如果我必须复制一个类别并将其分配给新的父级怎么办？因为这不是一个简单的插入语句。我必须插入所有子类别及其子类别等等。我如何跟踪他们的身份字段？我需要在插入时将parentCategoryId 分配给它们的子类别。

这个问题清楚吗？

Answer 1

不确定您是问 2 个问题还是仅问 1 个问题，即将整个类别复制为新类别（即复制类别）还是将现有类别重新分配给新的父类别 - 每个问题/解决方案都不同，但是让我们从复制整个类别开始。

首先，如果您使用基于身份的列，则在不使用“set Identity_insert on”选项的情况下，唯一的方法是将光标从根节点开始遍历整个树并向下工作（即插入顶级类别，获取新创建的标识值，插入第二级类别等）。如果您处于可以使用“set Identity_insert on”的场景，或者可以用显式数字替换身份的使用，那么您可以利用下面的代码。

在此代码中，您会注意到使用了 CTE、< a href="http://msdn.microsoft.com/en-us/library/ms186243.aspx" rel="nofollow noreferrer">递归 CTE 和 排名函数，因此假定 Sql 2005 或更高版本。此外，lvl、path 和 cnt 列仅出于演示目的而包含，您可以根据需要查看，但在任何最终解决方案中都不需要：

declare @root_category_id bigint,
        @start_new_id_value bigint;

-- What category id do we want to move?
select  @root_category_id = 3;
-- Get the current max id and pad a bit...
select  @start_new_id_value = max(categoryId)
from    Category;
select  @start_new_id_value = coalesce(@start_new_id_value,0) + 100;

-- Show our values
select  @root_category_id, @start_new_id_value;

begin tran;
set identity_insert Category on;

-- This query will give you the entire category tree
with subs (catId, parentCatId, catName, lvl, path, new_id, new_parent_id, cnt) as (
    -- Anchor member returns a row for the input manager
    select  catId, parentCatId, catName, 0 as lvl,
            cast(cast(catId as varchar(10)) as varchar(max)) as path,
            @start_new_id_value + row_number() over(order by catId) - 1 as new_id, 
            cast(parentCatId as bigint) as new_parent_id, 
            count(*) over(partition by 0) as cnt
    from    Category
    where   catId = @root_category_id

    union all

    -- recursive member returns next level of children
    select  c.catId, c.parentCatId, c.catName, p.lvl + 1,
            cast(p.path + '.' + cast(catId as varchar(10)) as varchar(max)),
            p.cnt + row_number() over(order by c.catId) + p.new_id - 1 as new_id,
            p.new_id as new_parent_id,
            count(*) over(partition by p.lvl) as cnt
    from    subs as p -- Parent
    join    Category as c -- Child
    on      c.parentCatId = p.catId
)
-- Perform the insert
insert  Category 
        (categoryId, Name, parentCategoryId)
select  s.catId, s.catName, s.parentCatId
from    subs s
--order by path;

set identity_insert Category off;
commit tran;

Answer 2

有趣的问题。

如果您使用的是 SQL 2005+，我想您可以构建一个包含要复制的类别的完整树的 CTE，这会将其放置在远离主表的临时位置中进行操作。

然后，您可以使用光标沿着树向下移动并更新父级的 ID 号...

现在我键入它，这似乎不是最有效的解决方案。也许您可以改为执行一个奇特的 SELECT 语句，该语句在执行 SELECT 操作时更新父 ID 的 ID？

Answer 3

您知道嵌套集合吗？这是在此类问题中表示数据的另一种方法。我不知道这是否有帮助，但如果您不知道，您可能需要考虑一下。 http://intelligent-enterprise.informationweek.com/001020/celko.jhtml