to_root.md

# 树结构溯根

现有一个表 node

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

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

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

<hr/>

点击进入[MySQL实战练习环境](https://mydev.csdn.net/product/pod/new?image=cimg-centos7-skilltreemysql&connect=auto&create=auto&utm_source=skill){target="_blank"}。

* `show databases;` 列出所有数据库
* `show tables;` 列出所有表

## 答案

```sql
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;
```

## 选项

### 没有递归定义

```sql
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;
```

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

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

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

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