SQL引用同一个表和查询中的主键

Question

我正在维护一个项目表(Id 是主键)。有些 Item 有 Parent Item (这也是一个 Item，Parent 列保存父 Item 的 Id，它也在同一个表中。有些 Item 没有父级，因此该值设置为 null。

是否有任何方法或适当的设计模式来维护此类信息。 (如:外键也来自同一个表)

如果给定了某个项目的 ID，建议如何编写循环直到项目的父项为 null 的查询。

eg: Given value is 5, the query should return 2.

Parent of 5 is 4, parent of 4 is 3, parent of 3 is 2 and 2's parent is null

|---------------------|------------------|
|          Id         |       Parent     |
|---------------------|------------------|
|           1         |          4       |
|---------------------|------------------|
|           2         |       null       |
|---------------------|------------------|
|           3         |          2       |
|---------------------|------------------|
|           4         |          3       |
|---------------------|------------------|
|           5         |          4       |
|---------------------|------------------|
|           6         |       null       |

I am working on a PostgreSQL database, but I beleive the solution is generic and may support SQL Server, MySQL, SQLite or Oracle

Answer 1

您可以使用递归查询来查询分层表:

with cte(id, parent) as
(
    select id, parent from mytable where id = :id -- <== the starting ID here
    union all
    select m.id, m.parent from cte join mytable m on m.id = cte.parent
)
select id from cte where parent is null;