今天在业务中,需要对sql ,进行修改,之前的sql 是跑在Oracle中,现在跑在MySQL上
1. 在Oracle中sql 语句如下
SELECT UUID, SUBSTR(XS,2,length(XS)) as ZTC, MC,XSPY,PARENTID FROM OA_ZTC where (xs like '%工业%' or XSPY like '%工业%') START WITH UUID IN (SELECT UUID FROM OA_ZTC WHERE PARENTID ='1' )CONNECT BY PRIOR uuid = PARENTID
2 . 在MySQL中为了实现同样的功能
with RECURSIVE cte as (
SELECT UUID, SUBSTR(XS,2,length(XS)) as ZTC, MC,XSPY,PARENTID FROM OA_ZTC where xs like '%工业%' or xspy like '%工业%' and UUID IN (SELECT UUID FROM OA_ZTC WHERE PARENTID ='1')
union all
SELECT a.UUID, SUBSTR(a.XS,2,length(a.XS)) as ZTC, a.MC,a.XSPY,a.PARENTID FROM OA_ZTC a,cte c where c.uuid = a.PARENTID
) select UUID,ZTC, MC,XSPY,PARENTID from cte