sql迭代 因为parentid is null 要有parentid为null的记录

用这种迭代，由于有start with parentid is null，所以第一行的查询必须有t.parentid is null的记录，如果被条件过滤掉了就加or t.parentid is null,

在用连接过滤时，如果有重复用distinct(只有放在第一列才不会出错),没有的列 要用常量补齐（0 leave1，不用'0'是因为和数据类型要匹配，也可直接用null）

select * from (select * from 

(   

     select distinct(t.id) id, t.name name,t.parentid  parentid ,0 leave1,'1' leave from opm_menufolder t left join  opm_menuitem oo on t.id=oo.menufolderid where oo.id in (select MenuItemId from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030') or t.parentid is null

   union all select distinct(o.id) id, o.name name,o.menufolderid ,o.level1 leave1,'2' leave  from opm_menuitem o  where o.id in (select MenuItemId from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030') 

   union all select distinct(l.id) id, l.name name,l.menuitemid ,l.level1 leave1,'3' leave  from opm_menuitemlimit l where l.id in (select MENUITEMLIMITID from OPM_ROLELIMIT where ROLEID='ff8080814dc41486014dcd88e7830030')

  

) 

start with parentid is null connect by prior id=parentid ORDER SIBLINGS BY parentid) 

 

 

 

select * from (select * from 

(   

     select t.name name,t.id  id,t.parentid  parentid ,0 leave1,'1' leave ,null uri from opm_menufolder t 

   union all select o.name name,o.id id,o.menufolderid,o.level1 leave1,'2' leave, o.uri uri from opm_menuitem o

   union all select l.name name,l.id id,l.menuitemid ,l.level1 leave1,'3' leave , l.uri uri from opm_menuitemlimit l

) 

start with parentid is null connect by prior id=parentid ORDER SIBLINGS BY parentid)