《Teradata数据库递归子查询&跨行拼接》
SQL递归原理
- A:原表S 和游标C ,目标表T,设定好游标作为驱动。
- B:游标和目标表关联,结果插入T表,同时更新游标C。
- C:用更细过的C再关联S表。
- D:重复B,C直达返回结果为空(或到达递归最高次数)
- E:递归结束后,返回T表
简单的迭代拼接
create multiset volatile table t1 (
Pid int
,val char(10)
) on commit preserve rows;
insert into t1 values (1,’a’);
insert into t1 values (2,’b’);
insert into t1 values (3,’c’);
insert into t1 values (4,’d’);
with recursive c1 as (
Select. —驱动游标
pid
,val
,cast(val as varchar(100)) as pat
from t1
union all
Select
c1.pid+1
,t1.val
,trim(c1.pat)||trim(t1.val) —拼接结果需要Trim防止报错
From t1 inner join c1
On c1.pid=t1.pid
)
Select * from c1 order by 1,2;
—结果