MySQL中递归查询

最新推荐文章于 2022-06-24 14:14:55 发布

原创最新推荐文章于 2022-06-24 14:14:55 发布 · 293 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Mysql #递归查询 #Oracle #with recursive

Oracle 同时被 3 个专栏收录

24 篇文章 ¥59.90 ¥99.00

订阅专栏

超级会员免费看

工作日志

19 篇文章 ¥79.90 ¥99.00

订阅专栏

超级会员免费看

MySQL

5 篇文章

订阅专栏

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