注意:MySQL8中才能使用该方法
向下递归查询(由父查子)
思路:
知道父亲查儿子
1、初始条件为知道一条父亲的记录,直接先放到temp表中,作为递归初始条件。
2、根据temp表里的父亲的这条记录里面的id(temp.id), 去原表sys_info o 里面,寻找原表记录中的pid(o.pid) 等于这位父亲的id即temp.id,取出来,放到temp表中
3、递归结束后,temp表就是查出来的儿子们
WITH recursive temp(id,name,pid )
AS (
SELECT s.id, s.name, s.pid
FROM sys_info s
WHERE s.id = '00031'
UNION ALL
SELECT o.id, o.name, o.pid
FROM temp, sys_info o
WHERE temp.id = o.pid
)
SELECT * FROM temp
对应Oracle查询
select m.id
from sys_info m
start with m.m.id='00031'
connect by prior m.id = m.pid
向上递归查询(由子查父)
知道儿子查父亲
1、初始条件为知道一条儿子的记录,直接先放到temp表中,作为递归初始条件。
2、根据temp表里的儿子的这条记录里面的pid(temp.pid),去原表sys_info o里面,寻找原表记录中的id(o.id) 等于这位儿子的pid即temp.pid,取出来,放到temp表中
3、递归结束后,temp表就是查出来的爸爸们
WITH recursive temp(id,name,pid )
AS
(
SELECT s.id, s.name, s.pid
FROM sys_info s
WHERE s.id = '00031'
UNION ALL
SELECT o.id, o.name, o.pid
FROM temp, sys_info o
WHERE temp.pid = o.id
)
SELECT * FROM temp
对应Oracle查询
select m.id
from sys_info m
start with m.m.id='00031'
connect by prior m.pid = m.id
在父亲表里面显示儿子们
WITH recursive temp (id,name,pid,childId,childName)
AS
(
SELECT s.id,s.name,s.pid,s.id childId,s.name childName
FROM sys_info s
WHERE s.id='00031'
UNION ALL
SELECT o.id,o.name,o.pid,temp.childId,temp.childName
FROM temp,sys_info o
WHERE temp.pid = o.id
)
SELECT * FROM temp