关于在sql语句中直接递归实现（此文章基于pgsql实现）

WITH RECURSIVE
        T(module_id,module_type_id,module_name,module_url,module_desc,parent_id,is_leaf,
        module_order,is_deleted,creator_id,create_time,modifier_id,modify_time,app_id)
        AS
        (
        SELECT
        module_id,module_type_id,module_name,module_url,module_desc, parent_id,is_leaf,
        module_order,is_deleted,creator_id,create_time,modifier_id,modify_time,app_id
        FROM sec_module WHERE app_id = #{appId,jdbcType=VARCHAR}
        UNION
        SELECT
        k.module_id,k.module_type_id,k.module_name,k.module_url,k.module_desc,k.parent_id,k.is_leaf,
        k.module_order,k.is_deleted,k.creator_id,k.create_time,k.modifier_id,k.modify_time,k.app_id
        FROM sec_module k INNER JOIN T ON T.module_id = k.parent_id
        )

        SELECT
        a.module_id as moduleId, a.module_type_id as moduleTypeId, a.module_name as moduleName,
        a.module_url as moduleUrl, a.module_desc as moduleDesc, a.parent_id as parentId, a.is_leaf as isLeaf,
        a.module_order as moduleOrder, a.is_deleted as isDeleted, a.app_id as appId,
        b.user_name as creatorUser, a.create_time as createTime,
        c.user_name as modifierUser, a.modify_time as modifyTime
        FROM T as a
        left join sec_user as b on b.user_id = a.creator_id
        left join sec_user as c on c.user_id = a.modifier_id
        WHERE a.is_deleted = '0'

 

此sql递归方式很简单，就是通过一个临时表查询。应用场景也为上下层级关系查询。