根据邻接表数据生成闭包表，树结构表设计，支持MySQL，Oracle，DB2

用一句SQL查询邻接表生成闭包表数据

最常见的树结构表是邻接表，包含id、pid字段，比如区域表（国、省、市、区），通常用CTE递归查询节点的所有子节点和父节点。
另一种常见的设计方式是闭包表，能直接查询节点的所有子节点和父节点。

一般通过邻接表生成闭包表的数据，下面举例说明。

邻接表数据
在这里插入图片描述

对应的闭包表数据
在这里插入图片描述

MySQL实现

# MYSQL版本，需要MySQL 8以上
WITH RECURSIVE
    area (ID, PID, AREA_LEVEL) AS (
        SELECT '北京市', '中国', 1
        UNION ALL
        SELECT '上海市', '中国', 1
        UNION ALL
        SELECT '徐汇区', '上海市', 2
        UNION ALL
        SELECT '漕河泾', '徐汇区', 3
    ),
    all_child (ID, PID, AREA_LEVEL) AS (
        SELECT ID, PID, AREA_LEVEL
        FROM area
        WHERE PID = '中国'
#          如果只生成徐汇区的数据，请改用下面的条件
#          WHERE ID = '徐汇区'
        UNION ALL
        SELECT area.ID, area.PID, area.AREA_LEVEL
        FROM area,
             all_child
        WHERE area.PID = all_child.ID
    ),
    all_parent (ID, B_PID, B_AREA_LEVEL, PID, AREA_LEVEL) AS (
        SELECT ID, ID, 0, PID, AREA_LEVEL
        FROM all_child
        UNION ALL
        SELECT all_parent.ID, area.PID, all_parent.B_AREA_LEVEL + 1, all_parent.PID, all_parent.AREA_LEVEL
        FROM area,
             all_parent
        WHERE area.ID = all_parent.B_PID
    )
SELECT ID,
       B_PID
#        B_AREA_LEVEL,
#        PID,
#        AREA_LEVEL
FROM all_parent
ORDER BY AREA_LEVEL, ID DESC, B_AREA_LEVEL DESC;

Oracle实现

-- Oracle版本，需要Oracle 11以上
WITH area (ID, PID, AREA_LEVEL) AS (
    SELECT '北京市', '中国', 1
    FROM DUAL
    UNION ALL
    SELECT '上海市', '中国', 1
    FROM DUAL
    UNION ALL
    SELECT '徐汇区', '上海市', 2
    FROM DUAL
    UNION ALL
    SELECT '漕河泾', '徐汇区', 3
    FROM DUAL
),
     all_child (ID, PID, AREA_LEVEL) AS (
         SELECT ID, PID, AREA_LEVEL
         FROM area
         WHERE PID = '中国'
--          如果只生成徐汇区的数据，请改用下面的条件
--          WHERE ID = '徐汇区'
         UNION ALL
         SELECT area.ID, area.PID, area.AREA_LEVEL
         FROM area,
              all_child
         WHERE area.PID = all_child.ID
     ),
     all_parent (ID, B_PID, B_AREA_LEVEL, PID, AREA_LEVEL) AS (
         SELECT ID, ID, 0, PID, AREA_LEVEL
         FROM all_child
         UNION ALL
         SELECT all_parent.ID, area.PID, all_parent.B_AREA_LEVEL + 1, all_parent.PID, all_parent.AREA_LEVEL
         FROM area,
              all_parent
         WHERE area.ID = all_parent.B_PID
     )
SELECT ID,
       B_PID
--        B_AREA_LEVEL,
--        PID,
--        AREA_LEVEL
FROM all_parent
ORDER BY AREA_LEVEL, ID DESC, B_AREA_LEVEL DESC;

DB2实现

-- DB2版本
WITH area (ID, PID, AREA_LEVEL) AS (
    VALUES ('北京市', '中国', 1),
           ('上海市', '中国', 1),
           ('徐汇区', '上海市', 2),
           ('漕河泾', '徐汇区', 3)
),
     all_child (ID, PID, AREA_LEVEL) AS (
         SELECT ID, PID, AREA_LEVEL
         FROM area
         WHERE PID = '中国'
--          如果只生成徐汇区的数据，请改用下面的条件
--          WHERE ID = '徐汇区'
         UNION ALL
         SELECT area.ID, area.PID, area.AREA_LEVEL
         FROM area,
              all_child
         WHERE area.PID = all_child.ID
     ),
     all_parent (ID, B_PID, B_AREA_LEVEL, PID, AREA_LEVEL) AS (
         SELECT ID, ID, 0, PID, AREA_LEVEL
         FROM all_child
         UNION ALL
         SELECT all_parent.ID, area.PID, all_parent.B_AREA_LEVEL + 1, all_parent.PID, all_parent.AREA_LEVEL
         FROM area,
              all_parent
         WHERE area.ID = all_parent.B_PID
     )
SELECT ID,
       B_PID
--        B_AREA_LEVEL,
--        PID,
--        AREA_LEVEL
FROM all_parent
ORDER BY AREA_LEVEL, ID DESC, B_AREA_LEVEL DESC;