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 parent_id = '1000000' -- 1000000是参数
union all select a.id, a.parent_id from a where a.id is not null
)
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
)