英文:
How to get next child of root as the parent via stored procedure?
问题
表 `Label` (IdLabel 整数, IdParentLabel 整数, Name 字符串(30))
插入到 Label 值 (1, null, 'root')
插入到 Label 值 (2, 1, 'child1')
插入到 Label 值 (3, 1, 'child2')
插入到 Label 值 (4, 1, 'child3')
插入到 Label 值 (5, 2, 'grandchild1')
插入到 Label 值 (6, 3, 'grandchild2')
插入到 Label 值 (7, 4, 'grandchild3')
插入到 Label 值 (8, 5, 'grandgrandchild1')
插入到 Label 值 (9, 5, 'grandgrandchild2')
我想编写一个存储过程，它将以 `@IdLabel` 作为输入参数，并将返回顶层父级的 `IdLabel`（顶层父级的定义：在分支中，根下的下一个子级，因此根永远不会被视为顶层父级），但它总是返回根作为父级。
示例：
* `执行 存储过程 8` 将返回 2，因为它是下一个子级
* `执行 存储过程 9` 也返回 2
* `执行 存储过程 6` 将返回 3
这是存储过程：
创建过程 检查父级
@IdLabel 整数
作为
开始
声明 @TopParent 整数
从 Label 中选择 @TopParent = IdParentLabel
其中 IdLabel = @IdLabel
当 @TopParent 不是 null 且 @TopParent <> 1 时
开始
将 @IdLabel 设为 @TopParent
从 Label 中选择 @TopParent = IdParentLabel
其中 IdLabel = @IdLabel
结束
如果 @TopParent 是 null
开始
从 Label 中选择 @TopParent = IdLabel
其中 IdParentLabel 是 null 且 IdLabel <> 1
按 IdLabel 排序
偏移 0 行
获取 下一行 1 行 只有
结束
选择 @TopParent 作为 顶层父级
结束
英文:
Table Label (IdLabel int, IdParentLabel int , Name varchar(30))
Insert Into Label Values (1, null, 'root')
Insert Into Label Values (2, 1, 'child1')
Insert Into Label Values (3, 1, 'child2')
Insert Into Label Values (4, 1, 'child3')
Insert Into Label Values (5, 2, 'grandchild1')
Insert Into Label Values (6, 3, 'grandchild2')
Insert Into Label Values (7, 4, 'grandchild3')
Insert Into Label Values (8, 5, 'grandgrandchild1')
Insert Into Label Values (9, 5, 'grandgrandchild2')
I want to write a stored procedure which will take @IdLabel as input parameter and will return the top most parent's IdLabel (definition of top most parent: the next child of root among the branches, so root will never be considered as top parent), but it is always returning the root as the parent.
Sample:
Exec StoredProcedure 8will return 2 as it is the next childExec StoredProcedure 9also returns 2Exec StoredProcedure 6will return 3
This is the stored procedure:
CREATE PROCEDURE CheckParent
@IdLabel int
AS
BEGIN
DECLARE @TopParent int
SELECT @TopParent = IdParentLabel
FROM Label
WHERE IdLabel = @IdLabel
WHILE @TopParent IS NOT NULL AND @TopParent <> 1
BEGIN
SET @IdLabel = @TopParent
SELECT @TopParent = IdParentLabel
FROM Label
WHERE IdLabel = @IdLabel
END
IF @TopParent IS NULL
BEGIN
SELECT @TopParent = IdLabel
FROM Label
WHERE IdParentLabel IS NULL AND IdLabel <> 1
ORDER BY IdLabel
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY
END
SELECT @TopParent AS TopParent
END
答案1
得分: 3
I'm not sure why you're using a looping architecture, you can use the built-in recursive nature of CTEs for this type of task, for example
declare @IdLabel int = 9;
with p as (
select IdLabel, IdParentLabel
from label
where idLabel = @IdLabel
union all
select l.IdLabel, l.IdParentLabel
from Label l
join p on l.IdLabel = p.IdParentLabel
where l.idParentLabel is not null
)
select top(1) idLabel
from p
order by IdParentLabel;
英文:
I'm not sure why you're using a looping architecture, you can use the built-in recursive nature of CTEs for this type of task, for example
declare @IdLabel int = 9;
with p as (
select IdLabel, IdParentLabel
from label
where idLabel = @IdLabel
union all
select l.IdLabel, l.IdParentLabel
from Label l
join p on l.IdLabel = p.IdParentLabel
where l.idParentLabel is not null
)
select top(1) idLabel
from p
order by IdParentLabel;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论