Oracle和Mysql根据子节点查父节点

目的：根据子级遍历查出所其所有父级 例如：

Oracle方式

关键函数：LISTAGG．．．WITHIN GROUP　、　start with．．．connect by

select
   (select LISTAGG(menu_name,'-') WITHIN GROUP( ORDER BY menu_code) 
       from t_s_bf_menu_info
       start with menu_code = t.menu_code connect by prior menu_code_p = menu_code) as menuName
from t_s_bf_menu_info t
where
    t.menu_code in ("a","b")
order by menu_code

Mysql方式

mysql 8.0 + ：可以直接用 WITH　RECURSIVE 　实现递归，以下是参考链接

参考1
参考2

 WITH RECURSIVE a(menu_code, menu_name, menu_code_p, path) AS (
                SELECT
                menu_code,
                menu_name,
                menu_code_p,
                menu_name AS path
                FROM
                t_s_bf_menu_info
                WHERE
                menu_code_p IS NULL
            UNION ALL
                SELECT
                b.menu_code,
                b.menu_name,
                b.menu_code_p,
                concat( a.path, "-", b.menu_name ) AS path
                FROM
                a,
                t_s_bf_menu_info b
                WHERE
                a.menu_code = b.menu_code_p
) SELECT path
   FROM a
   where
       a.menu_code in ("a","b")

反思
１、实现过程中，ｍｙｓｑｌ方式实现LISTAGG，找到group_concat() 函数

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
例如：
group_concat( menu_code　order by menu_code desc　separator '－' ) 

２、递归方式，通过子节点找父节点，但是只能查一个子节点

 WITH RECURSIVE org AS (
        SELECT a.*
        FROM t_s_bf_organization a
        WHERE a.org_frame_code = #{orgFrameCode, jdbcType=VARCHAR}
      UNION ALL
        SELECT k.*
        FROM t_s_bf_organization k
        INNER JOIN org c ON c.org_frame_code_p = k.org_frame_code
      ) SELECT *
      FROM org