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;