sql - 如何获得父母的所有孩子

Question

我想从一张桌子上拿走所有含有肉类的东西。将来可以为组（父 ID）创建另一个元素（子 ID），例如现在食物 -> 肉类 -> 动物肉类 -> 猪。将来有人可以创建另一个带有父 ID 猪的记录，例如这种食物 -> 肉类 -> 动物肉 -> 猪 -> 培根，下一个人可以创建另一个带有父 ID 培根等的记录。我如何查询谁会接受最高父级为 MEATS 的所有元素？所以查询应该返回元素猪、牛、培根。

+---------+----------+--------------+
| childId | parentId | name         |
+---------+----------+--------------+
| 1       | 0        | FOOD         |
+---------+----------+--------------+
| 2       | 1        | FRUITS       |
+---------+----------+--------------+
| 3       | 1        | VEGETABLES   |
+---------+----------+--------------+
| 4       | 1        | MEATS        |
+---------+----------+--------------+
| 5       | 2        | apple        |
+---------+----------+--------------+
| 6       | 2        | banana       |
+---------+----------+--------------+
| 7       | 3        | carrot       |
+---------+----------+--------------+
| 8       | 3        | tomatoes     |
+---------+----------+--------------+
| 9       | 4        | FISH MEATS   |
+---------+----------+--------------+
| 10      | 4        | ANIMAL MEATS |
+---------+----------+--------------+
| 11      | 9        | cod          |
+---------+----------+--------------+
| 12      | 9        | salmon       |
+---------+----------+--------------+
| 13      | 10       | cow          |
+---------+----------+--------------+
| 14      | 10       | pig          |
+---------+----------+--------------+

标签： sqlsql-servertsql

Answer 1

递归 CTE（公用表表达式）将完成这项工作：

with
x as (
  select childId, parentId, name from t where id = 3
  union all
  select t.childId, t.parentId, t.name
  from x
  join t on t.parentId = x.childId
)
select * from x