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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值