to_root.md

# 树结构溯根

现有一个表 node

```mysql
create table node
(
    id      int primary key auto_increment,
    pid     integer,
    val integer
);
```

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

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

## 答案

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

## 选项

### 没有递归定义

```mysql
with t as (
    select id, pid, val
    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, node.val
from node
         join t on node.id = t.id;
```

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

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

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

```mysql
select node.id, node.pid, node val
from node as t
where t.pid = (select id from t where id = t.pid)
```