【数据库】MySQL实现Oracle递归中的sys_connect_by_path函数

Oracle实例：

            SELECT c_code orgid,
                   c_name org_name,
                   LEVEL lvl,
                   sys_connect_by_path(c_code, '/') org_path,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 1) org0,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 2) org1,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 3) org2,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 4) org3,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 5) org4,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 6) org5,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 7) org6,
                   regexp_substr(sys_connect_by_path(c_code, '/'),
                                 '[^/]+',
                                 1,
                                 8) org7
              FROM bas_sys_org
             START WITH c_code = '00003'
            connect BY prior c_code = c_parentcode

转成MySQL语法：

WITH recursive temp(c_code, c_name, lvl,  rownum, org_path1) AS
 (
	SELECT s.c_code, s.c_name, 1 lvl, @rownum := 1 rn ,  CAST(c_code AS CHAR) sdfsf1 FROM  BAS_SYS_ORG s WHERE s.c_code ='00003'
	UNION ALL
	SELECT o.c_code, o.c_name, temp.lvl + 1, @rownum := @rownum + 1 rn, CONCAT(temp.org_path1, '/', o.c_code) st1  FROM temp,BAS_SYS_ORG o WHERE temp.c_code=o.c_parentcode
)
SELECT temp.c_code orgid, temp.c_name org_name, temp.lvl ,CONCAT('/',temp.org_path1) org_path,
regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 1) org0,
regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 2) org1,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 3) org2,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 4) org3,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 5) org4,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 6) org5,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 7) org6,
                   regexp_substr(temp.org_path1,
                                 '[^/]+',
                                 1,
                                 8) org7 
 FROM temp

参考资料：mysql8 实现connect by语法_nayi_224的博客-CSDN博客_connectby mysql 

#网上示例
WITH recursive tab1(id, parent_id, lv, rownum, order_str) AS (
SELECT t0.*, 1 lv, @rownum := 1 rn, CAST(@rownum AS CHAR) sdfsf FROM blog_temp.etc_test t0 WHERE t0.parent_id IS NULL  
 UNION ALL
SELECT t2.id, t2.parent_id, t1.lv + 1, @rownum := @rownum + 1 rn, CONCAT(t1.order_str, '-', @rownum) st 
  FROM blog_temp.etc_test t2, tab1 t1  
 WHERE t2.parent_id = t1.id
)
SELECT t1.*,
    CONCAT(LPAD('-', (lv - 1) * 2, '-'), t1.id) tree
  FROM tab1 t1
 ORDER BY t1.order_str;