父子链的SQL查询

发布于 2024-10-02 18:38:11 字数 350 浏览 11 评论 0原文

我有一个表，可以将表中的另一个成员引用为父级。该父级还可以将另一行引用为其父级......依此类推。

id     col1     col2    parentID
1      foo      bar       NULL
2      blah     boo       1
3      fob      far       2
4      wob      lob       NULL

我想返回给定 id 的链。因此，如果 id 为 3，我将返回第 3 行、第 2 行和第 1 行。如果 id 为 2，我将返回第 2 行和第 1 行。如果 id 为 1 或 4，我将仅返回该行。

谢谢

原文

I have a single table that can refer to one other member in the table as a parent. That parent could also refer to one other row as its parent...and so on.

id     col1     col2    parentID
1      foo      bar       NULL
2      blah     boo       1
3      fob      far       2
4      wob      lob       NULL

I would like to return the chain given an id. So if the id were 3 I would return row 3, row 2 and row 1. If id was 2 I would return row 2 and row 1. If the id were 1 or 4 I would just return that row.

thank you

分享到QQ

分享到微博

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

发布评论

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

野生奥特曼 2024-10-09 18:38:12

使用递归CTE：

DECLARE @id INT
    SET @id = 3

;WITH hierarchy AS (
  SELECT t.id, t.parentid
    FROM YOUR_TABLE t
   WHERE t.id = @id
 UNION ALL
 SELECT x.id, x.parentid
   FROM YOUR_TABLE x
   JOIN hierarchy h ON h.parentid = x.id)
SELECT h.id
  FROM hierarchy h

结果：

id
---
3
2
1

Use a recursive CTE:

DECLARE @id INT
    SET @id = 3

;WITH hierarchy AS (
  SELECT t.id, t.parentid
    FROM YOUR_TABLE t
   WHERE t.id = @id
 UNION ALL
 SELECT x.id, x.parentid
   FROM YOUR_TABLE x
   JOIN hierarchy h ON h.parentid = x.id)
SELECT h.id
  FROM hierarchy h

Results:

id
---
3
2
1

回复收藏 0 原文

把人绕傻吧 2024-10-09 18:38:12

如果您使用递归 CTE，请不要忘记添加

h.parentid <> x.id

连接

JOIN hierarchy h ON h.parentid = x.id)

，否则您将只是一个 最大递归 - 错误，因为它会循环

IF you use a recursive CTE, don't forget to add

h.parentid <> x.id

on your join

JOIN hierarchy h ON h.parentid = x.id)

else you will just a The maximum recursion-error since it loops

回复收藏 0 原文

酒儿 2024-10-09 18:38:12

干得好

SELECT P.cat_id AS parent_cat_id, P.parent_id AS ROOT, P.cat_name AS parent_cat_name, C.parent_id, C.cat_id, C.cat_name FROM categories AS P LEFT OUTER JOIN categories AS C ON C.parent_id=P.cat_id WHERE P.parent_id IS NULL ORDER BY parent_cat_name, cat_name

Here you go

SELECT P.cat_id AS parent_cat_id, P.parent_id AS ROOT, P.cat_name AS parent_cat_name, C.parent_id, C.cat_id, C.cat_name FROM categories AS P LEFT OUTER JOIN categories AS C ON C.parent_id=P.cat_id WHERE P.parent_id IS NULL ORDER BY parent_cat_name, cat_name

回复收藏 0 原文

故事灯 2024-10-09 18:38:12

WITH Hierarchy(ChildId, ChildName, Generation, ParentId)
AS
(
    SELECT Id, Name, 0, ParentId
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL        
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId

        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
    OPTION(MAXRECURSION 32767)

WITH Hierarchy(ChildId, ChildName, Generation, ParentId)
AS
(
    SELECT Id, Name, 0, ParentId
        FROM UserType AS FirtGeneration
        WHERE ParentId IS NULL        
    UNION ALL
    SELECT NextGeneration.Id, NextGeneration.Name, Parent.Generation + 1, Parent.ChildId

        FROM UserType AS NextGeneration
        INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
    FROM Hierarchy
    OPTION(MAXRECURSION 32767)

回复收藏 0 原文

~没有更多了~