KingbaseES的SQL语句-CTE递归

背景

从上下级关系表中，任意一个节点数据出发，可以获得该节点的上级或下级。CTE的递归语法，或者 connect by 与 start with的 查询语法，能够实现这个需求。

当我们需要制作上下级关系的数据时，也可以使用CTE的递归语法。

举例

一个父节点拥有随机数量的子节点，通过控制层级数和随机数的上限，可以实现海量的数据集合。

drop table t_level1;create table t_level1(    id  int primary key,    pid int);create index t_level1_pid on t_level1 (pid);--Sequence For Primary keycreate sequence seq_level1;select count(*) from t_level1;truncate t_level1;--方法一：使用表作为record type，生成record数组with recursive rec as (    --Root Node    select array_agg((id, pid)::t_level3) as recs,           5                              as max_lvl, --最大level           5                              as rec_cnt  --最大子节点数    from (select nextval('seq_level1') as id, null as pid) t    union all    --Children Node, Random Number    select array_agg((id, pid)::t_level3) as recs,           recp.max_lvl                   as max_lvl,           rec_cnt                        as rec_cnt    from (select (unnest(rec.recs)).id as pid,                 rec.max_lvl - 1       as max_lvl,                 rec_cnt               as rec_cnt          from rec          where rec.max_lvl > 1) as recp       , lateral (select nextval('seq_level1') as id                  from generate_series(1, (random() * rec_cnt)::int)                  where pid > 0) t2    group by pid, max_lvl, rec_cnt)select (unnest(recs)).*from rec;--方法二：使用主键作为type，生成主键类型数组with recursive rec as (    --Root Node    select array_agg(nextval('seq_level1')) as ids,           null::bigint                     as pid,           5                                as max_lvl, --最大level           5                               as rec_cnt  --最大子节点数     union all    --Children Node, Random Number    select (select array_agg(nextval('seq_level1')) as ids            from generate_series(1, (random() * rec_cnt)::int)           )       as ids,           pid     as pid,           max_lvl as max_lvl,           rec_cnt as rec_cnt    from (select unnest(rec.ids) as pid,                 rec.max_lvl - 1 as max_lvl,                 rec_cnt         as rec_cnt          from rec          where rec.max_lvl > 1) as recp)select unnest(ids) as id, pid, max_lvlfrom rec;