Oracle或者MySQL根据父ID查询所有子ID的数据,树型结构通用

Oracle或者MySQL根据父ID查询所有子ID的数据,树型结构通用

本文连接:https://www.cnblogs.com/muphy/p/14858149.html

 

测试表数据如下

  

Oracle with递归子查询

with a(id, parent_id) as
(
select m.id, m.parent_id from sys_menu m where m.id = '1000000'
union all select a.id, a.parent_id from a inner join sys_menu d on a.id = d.parent_id
)
select * from sys_menu t inner join a on a.id = t.id;

 

MySQL

select * from sys_menu m where m.id in (
 select id from (
   select t1.id,if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
    from (select id,parent_id from sys_menu t order by parent_id, id) t1, (select @pids := '1000000') t2  -- 1000000 是参数
 ) t3 where ischild != 0
)

 

 mysql with递归子查询

with recursive a( id ) as 
(
    select id from sys_menu where id = '1000000' 
    union all select d.id id from a inner join sys_menu d on a.id = d.parent_id 
)
select * from sys_menu where id in ( select * from a )

 

posted @ 2021-06-07 11:27  明月心~  阅读(658)  评论(0编辑  收藏  举报