递归查询

实现思路

1. sql 中WITH xxxx AS () 是对一个查询子句做别名,同时数据库会对该子句生成临时表；
2. WITH RECURSIVE 则是一个递归的查询子句,他会把查询出来的结果再次代入到查询子句中继续查询.

父级递归查询子集

WITH RECURSIVE cte as (
 select id,name,parent_id from '要查询的表' where id = '父级ID'
 union all
 select temp.id,temp.name,temp.parent_id from  '要查询的表' temp INNER JOIN  cte c  on temp.parent_id = c.id
)
select * from cte

子级递归查询父级

WITH RECURSIVE cte as (
 select id,name,parent_id from '要查询的表' where id = '子集ID'
 union all
 select temp.id,temp.name,temp.parent_id from  '要查询的表' temp INNER JOIN  cte c  on temp.id = c.parent_id
)
select * from cte