MySQL中递归查询

今天在业务中，需要对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