H2数据库递归语法

-- 创建模块表
create table module (
        sid number(10) not null, 
        module_name varchar2(20) not null, 
        pid number(10)
);

-- 插入数据
insert into module(sid, module_name, pid) values (1, '模块1', null);
insert into module(sid, module_name, pid) values (2, '模块2', null);
insert into module(sid, module_name, pid) values (3, '模块3', null);
insert into module(sid, module_name, pid) values (4, '模块21', 2);
insert into module(sid, module_name, pid) values (5, '模块31', 3);
insert into module(sid, module_name, pid) values (6, '模块211', 4);
insert into module(sid, module_name, pid) values (7, '模块11', 1);
insert into module(sid, module_name, pid) values (8, '模块111', 7);
insert into module(sid, pid, module_name) values(9, 3,  '模块32');

-- 从头节点开始递归遍历
with tree(sid, pid, name) as (
        select m.sid, m.pid, m.module_name from module m where m.pid is null
        union all
        select m2.sid, m2.pid, m2.module_name 
                from tree inner join module m2 on tree.sid = m2.pid
) select * from tree order by sid;

-- 从指定节点开始递归遍历
with tree(sid, pid, name) as (
        select m.sid, m.pid, m.module_name from module m where m.sid = 2
        union all
        select m2.sid, m2.pid, m2.module_name 
                from tree inner join module m2 on tree.sid = m2.pid
) select * from tree order by sid;

-- 从叶节点开始方向递归遍历，注意distinct
with tree(sid, pid, name) as (
        select m.sid, m.pid, m.module_name from module m where m.sid in (5,6,8, 9)
        union all
        select distinct  m2.sid, m2.pid, m2.module_name 
                from tree inner join module m2 on tree.pid = m2.sid
) select * from tree order by sid;

-- 查询指定层级上的节点，先要遍历全部
with tree(sid, pid, name, level) as (
        select m.sid, m.pid, m.module_name, 0 from module m where m.pid is null
        union all
        select m2.sid, m2.pid, m2.module_name, tree.level + 1 
                from tree inner join module m2 on tree.sid = m2.pid
) select * from tree where level = 1 order by sid;

-- 注意with as...后只支持select语句，不支持如下级联删除
with tree(sid, pid, name) as (
        select m.sid, m.pid, m.module_name from module m where m.sid = 2
        union all
        select m2.sid, m2.pid, m2.module_name 
                from tree inner join module m2 on tree.sid = m2.pid
) delete from module where sid in (select sid from tree);