mysql8 实现 oracle 的 connect_by_root

原来老项目用的是 Oracle 进行的查询，语句如下：

SELECT CONNECT_BY_ROOT TRO_NUM     AS TRO_NUM,
       CONNECT_BY_ROOT TRO_NAME    AS TRO_NAME,
       MSATM.SID
FROM MA_ST_AUDIT_TRO_MAINT MSATM
WHERE MSATM.DELETED = 0
CONNECT BY PRIOR MSATM.SID = MSATM.PARENT_ID
START WITH PARENT_ID = 10175 ORDER BY SID

查询结果

不使用 CONNECT_BY_ROOT 函数的查询

改为 Mysql 8 后的语句

WITH RECURSIVE CTE AS (
    SELECT b.tro_num, b.tro_name, b.sid
    FROM MA_ST_AUDIT_TRO_MAINT b
    WHERE b.DELETED = 0
      AND b.parent_id = 10175
    UNION ALL
    SELECT c.tro_num, c.tro_name, d.sid
    FROM MA_ST_AUDIT_TRO_MAINT d
             INNER JOIN CTE c ON c.sid = d.PARENT_ID AND d.DELETED = 0
)
SELECT tro_num, tro_name, sid
FROM CTE
order by sid

实现效果

主要是使用 MySQL 8 的递归来实现该效果的。注意点