MySql递归查找父级

---

---

前言

单个表格中进行递归查询父级、获取到父级信息

一、建立数据表格、插入数据

1.创建表格

create table if not exists Student(
    ID int  primary key,
    PID int not null,
    Pname varchar(2),
    Grade int 
	);

2.插入数据

insert into Student(Pid,Pname,Grade) values (0,'第一等级',0)
insert into Student(Pid,Pname,Grade) value  (1,'第二等级',1)
insert into Student(Pid,Pname,Grade) value  (2,'第三等级',2)

二、开始写递归

代码如下（示例）：

SELECT t2.ID, t2.`Pname`
FROM
    (
        SELECT 
        @r AS _id,
        (SELECT @r := Pid FROM student WHERE ID = _id) AS Pid,
        @l := @l + 1 AS lvl
        FROM
        (SELECT @r := 3, @l := 0) vars, student AS h
    ) t1
JOIN student t2
ON t1._id = t2.ID ORDER BY t2.ID 

注释：数字3的位置是写子级的ID

---