人大金仓kingbase8的sql递归写法

背景：公司要迁移项目，原来项目是用mysql数据库，为了安全性需要把mysql数据库替换成国产数据库（人大金仓的kingbase8），由于两个数据库的兼容性问题，迁移应用后发现有些sql不能直接在kingbase上运行，需要修改sql的语法符合kingbase的语法要求。

表关系：部门表（sysmgr_dept），部门用户映射表（sysmgr_dept_user_mid），需要查询出来的数据结构为：按父子关系递归查询出该用户所属部门及其下属的所有部门名称集合。
原来在mysql中的写法：

SELECT
            t2.deptname
        FROM
            (
                SELECT
                    @r AS _id,
                (SELECT @r := parentid FROM sysmgr_dept WHERE id = _id ) AS parentid,
                @l :=@l + 1 AS lvl
                FROM
                    (
                    SELECT
                    @r := (SELECT
                    t.DEPTID
                    FROM
                    sysmgr_dept_user_mid t
                    WHERE
                    t.USERID = '1273228086501244928'
                    AND t.ISMASTER = '1'
                    LIMIT 1
                    ) ,@l := 0
                    ) vars,
                    sysmgr_dept h
                WHERE
                    @r <> 0
            ) t1
                JOIN sysmgr_dept t2 ON t1._id = t2.id
        ORDER BY
            t1.lvl DESC`

改成符合人大金仓后的写法：

SELECT
            t2.deptname
        FROM
            (
                WITH RECURSIVE ptemp as (
                    SELECT id as _id,parentid, 1 as lvl
                    from sysmgr_dept where id = (SELECT
                                                        t.DEPTID
                                                    FROM
                                                        sysmgr_dept_user_mid t
                                                    WHERE
                                                        t.USERID = '1273228086501244928'
                                                      AND t.ISMASTER = '1'
                    LIMIT 1)
        union all
        SELECT csd.id as _id,csd.parentid, ptemp.lvl + 1 from sysmgr_dept csd INNER JOIN ptemp on csd.id = ptemp.parentid
            ) SELECT * from ptemp ORDER BY ptemp.lvl
            ) t1
                JOIN sysmgr_dept t2 ON t1._id = t2.id
        ORDER BY
            t1.lvl DESC

两者的输出如下：

中间结构如下：