目的:根据子级遍历查出所其所有父级 例如:
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 实现递归,以下是参考链接
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")
反思
1、实现过程中,mysql方式实现LISTAGG,找到group_concat() 函数
语法:group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
例如:
group_concat( menu_code order by menu_code desc separator '-' )
2、递归方式,通过子节点找父节点,但是只能查一个子节点
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