MySql查询树结构表以及关联数据

MySQL从8.0版本开始支持WITH RECURSIVE语法。之前版本不支持下面SQL语句。

military_action_group 表树形结构，包含parentId 关联
military_action_group_weapon 关联
military_weapon

WITH RECURSIVE tree AS (
        SELECT g.id, g.parent_id, g.name
        FROM military_action_group g
        WHERE g.id = 89

        UNION ALL

        SELECT g.id, g.parent_id, g.name
        FROM military_action_group g
                INNER JOIN tree ON g.parent_id = tree.id
)
SELECT tree.id id,tree.parent_id parent_id, tree.name name, w.id w_id, w.name w_name, w.setting_id w_setting_id
FROM tree
        LEFT JOIN military_action_group_weapon gw ON tree.id = gw.group_id
        LEFT JOIN military_weapon w ON gw.weapon_id = w.id