如何通过存储过程获取根节点的下一个子节点作为父节点？

huangapple
go评论97阅读模式
英文:

How to get next child of root as the parent via stored procedure?

问题

  1. 表 `Label` (IdLabel 整数, IdParentLabel 整数, Name 字符串(30))
  3.     插入到 Label 值 (1, null, 'root')
  4.     插入到 Label 值 (2, 1, 'child1')
  5.     插入到 Label 值 (3, 1, 'child2')
  6.     插入到 Label 值 (4, 1, 'child3')
  7.     插入到 Label 值 (5, 2, 'grandchild1')
  8.     插入到 Label 值 (6, 3, 'grandchild2')
  9.     插入到 Label 值 (7, 4, 'grandchild3')
  10.     插入到 Label 值 (8, 5, 'grandgrandchild1')
  11.     插入到 Label 值 (9, 5, 'grandgrandchild2')
  13. 我想编写一个存储过程，它将以 `@IdLabel` 作为输入参数，并将返回顶层父级的 `IdLabel`（顶层父级的定义：在分支中，根下的下一个子级，因此根永远不会被视为顶层父级），但它总是返回根作为父级。
  15. 示例：
  17. * `执行 存储过程 8` 将返回 2，因为它是下一个子级
  18. * `执行 存储过程 9` 也返回 2
  19. * `执行 存储过程 6` 将返回 3
  21. 这是存储过程：
  23.     创建过程 检查父级
  24.         @IdLabel 整数
  25.     作为
  26.     开始
  27.         声明 @TopParent 整数
  28.         
  29.         从 Label 中选择 @TopParent = IdParentLabel
  30.         其中 IdLabel = @IdLabel
  31.         
  32.         当 @TopParent 不是 null 且 @TopParent <> 1 时
  33.         开始
  34.             将 @IdLabel 设为 @TopParent
  35.             
  36.             从 Label 中选择 @TopParent = IdParentLabel
  37.             其中 IdLabel = @IdLabel
  38.         结束
  39.         
  40.         如果 @TopParent 是 null
  41.         开始
  42.             从 Label 中选择 @TopParent = IdLabel
  43.             其中 IdParentLabel 是 null 且 IdLabel <> 1
  44.             按 IdLabel 排序
  45.             偏移 0 行
  46.             获取 下一行 1 行 只有
  47.         结束
  48.         
  49.         选择 @TopParent 作为 顶层父级
  50.     结束
英文:

Table Label (IdLabel int, IdParentLabel int , Name varchar(30))

  1. Insert Into Label Values (1, null, &#39;root&#39;)
  2. Insert Into Label Values (2, 1, &#39;child1&#39;)
  3. Insert Into Label Values (3, 1, &#39;child2&#39;)
  4. Insert Into Label Values (4, 1, &#39;child3&#39;)
  5. Insert Into Label Values (5, 2, &#39;grandchild1&#39;)
  6. Insert Into Label Values (6, 3, &#39;grandchild2&#39;) 
  7. Insert Into Label Values (7, 4, &#39;grandchild3&#39;) 
  8. Insert Into Label Values (8, 5, &#39;grandgrandchild1&#39;) 
  9. Insert Into Label Values (9, 5, &#39;grandgrandchild2&#39;)

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 8 will return 2 as it is the next child
  • Exec StoredProcedure 9 also returns 2
  • Exec StoredProcedure 6 will return 3

This is the stored procedure:

  1. CREATE PROCEDURE CheckParent
  2. @IdLabel int
  3. AS
  4. BEGIN
  5. DECLARE @TopParent int
  6. SELECT @TopParent = IdParentLabel
  7. FROM Label
  8. WHERE IdLabel = @IdLabel
  9. WHILE @TopParent IS NOT NULL AND @TopParent &lt;&gt; 1
  10. BEGIN
  11. SET @IdLabel = @TopParent
  12. SELECT @TopParent = IdParentLabel
  13. FROM Label
  14. WHERE IdLabel = @IdLabel
  15. END
  16. IF @TopParent IS NULL
  17. BEGIN
  18. SELECT @TopParent = IdLabel
  19. FROM Label
  20. WHERE IdParentLabel IS NULL AND IdLabel &lt;&gt; 1
  21. ORDER BY IdLabel
  22. OFFSET 0 ROWS
  23. FETCH NEXT 1 ROWS ONLY
  24. END
  25. SELECT @TopParent AS TopParent
  26. 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

  1. declare @IdLabel int = 9;
  3. with p as (
  4. 	select IdLabel, IdParentLabel
  5. 	from label
  6. 	where idLabel = @IdLabel
  7. 	union all
  8. 	select l.IdLabel, l.IdParentLabel
  9. 	from Label l
  10. 	join p on l.IdLabel = p.IdParentLabel
  11. 	where l.idParentLabel is not null
  12. )
  13. select top(1) idLabel
  14. from p
  15. 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

  1. declare @IdLabel int = 9;
  2. with p as (
  3. select IdLabel, IdParentLabel
  4. from label
  5. where idLabel = @IdLabel
  6. union all
  7. select l.IdLabel, l.IdParentLabel
  8. from Label l
  9. join p on l.IdLabel = p.IdParentLabel
  10. where l.idParentLabel is not null
  11. )
  12. select top(1) idLabel
  13. from p
  14. order by IdParentLabel;

huangapple
  • 本文由 发表于 2023年4月17日 00:28:06
  • 转载请务必保留本文链接：https://go.coder-hub.com/76029000.html
  • sql
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定