to_root.md

# 树结构溯根

现有一个表 node

```postgresql
create table node
(
    id      serial primary key,
    pid     integer,
    content text
);
```

其 pid 列引用 id 列，形成一个树结构，根节点的 pid 为 0。

现在我们希望写一个查询，找到某一个给定id的记录，其父节点、父节点的父节点，直至根节点的路径。那么这个查询应该是：

## 答案

```postgresql
with recursive t(id, pid, content) as (
    select id, pid, content
    from node
    where id = $1
    union all
    select node.id, node.pid, node.content
    from node
             join t on node.id = t.pid)
select node.id, node.pid, content
from node
         join t on node.id = t.id;
```

## 选项

### 没有递归定义

```postgresql
with t as (
    select id, pid, content
    from node
    where id = $1
    union all
    select node.id, node.pid, node.level
    from node
             join t on node.id = t.pid)
select node.id, node.pid, content
from node
         join t on node.id = t.id;
```

### 平凡的连接查询无法处理递归问题

```postgresql
select node.id, node.pid, node.content
from node
         join node as p on node.pid = p.id
where id = $1;
```

### 子查询无法处理递归问题

```postgresql
select node.id, node.pid, node content
from node as t
where t.pid = (select id from t where id = t.pid)
```