递归查询

MYSQL

1. 数据schema

create table region
(
    region_no    varchar(4)   not null
        primary key,
    content      varchar(255) not null,
    subregion_no varchar(4)   null
);

2. 模拟数据

create
    definer = root@`%` procedure create_region_data()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 10 DO
            INSERT INTO region (region_no, content, subregion_no) VALUES (i, CONCAT('region content', i), round(rand(10) * 10));
            SET i = i + 1;
        END WHILE;
END;

3.递归查询region以及下级的region(通过subregion_no)

WITH RECURSIVE cte_name (region_no, content, subregion_no) AS (
    SELECT region_no, content, subregion_no FROM region
                                            where region_no = #{regionNo}
    UNION ALL
    SELECT t.region_no, t.content, t.subregion_no
    FROM region t
    INNER JOIN cte_name ON t.region_no = cte_name.subregion_no
)
SELECT * FROM cte_name;

4.递归查询region以及上级的region(通过subregion_no)

WITH RECURSIVE cte_name (region_no, content, subregion_no) AS (
    SELECT region_no, content, subregion_no FROM region
    where region_no = #{regionNo}
    UNION ALL
    SELECT t.region_no, t.content, t.subregion_no
    FROM region t
             INNER JOIN cte_name ON t.subregion_no = cte_name.region_no
)
SELECT * FROM cte_name;

Oracle

Oracle直接使用start with <初始条件> connect by 递归条件

相关结构化查询详情 Hierarchical Queries

1. 语法描述: Description of the illustration hierarchical_query_clause

{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition
}

2. 查本级及下级数据

select region_no
   from region
start with region_no = #{regionNo} 
connect by region_no = prior subregion_no

3. 查本级及上级数据

select region_no
   from region
start with region_no = #{regionNo} 
connect by prior region_no = subregion_no