父子链的SQL查询 [英] SQL query for parent-child chain

问题描述

我有一个表，可以将表中的另一个成员称为父表.该父级也可以将另一行称为其父级...等等.

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

我想返回给定ID的链.因此，如果id为3，则返回第3行，第2行和第1行.如果id为2，则返回第2行和第1行.如果id为1或4，则返回该行.

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.

谢谢

推荐答案

使用递归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

这篇关于父子链的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！