由于Greenplum没有connect by子句,如果想实现类似功能,需要用with recursive,包括级联查询
set gp_recursive_cte_prototype to true;
with recursive chenxu(name_ , lv, s_name) as(
select name_, length(name_) lv,
substring(tt.name_,length(tt.name_) - length(name_) + 1 ,1)
from (select '中华人民共和国万岁' name_) tt
union all
select tt.name_, c.lv - 1,
substring(tt.name_,length(tt.name_) - c.lv +2 ,1)
from (select '中华人民共和国万岁' name_) tt
inner join chenxu c on lv > 1
)
select * from chenxu