递归CTE的SQL替换 [英] SQL replacement for Recursive CTE

问题描述

我有一个包含的表Test

I have a table Test which contains

TEST
----
tablename|columnvalue|rankofcolumn
A|C1|1
A|C2|2
A|C3|3
A|C4|4
B|CX1|1
B|CX2|2
C|CY1|1
C|CY2|2
C|CY3|3

我想按如下方式生成路径以及其他列

I want to generate the path along with other columns as follows

RESULT
----
tablename|columnvalue|rankofcolumn|path
A|C1|1|C1
A|C2|2|C1->C2
A|C3|3|C1->C2->C3
A|C4|4|C1->C2->C3->C4
B|CX1|1|CX1
B|CX2|2|CX1->CX2
C|CY1|1|CY1
C|CY2|2|CY1->CY2
C|CY3|3|CY1->CY2->CY3

根据此问题，我可以使用递归CTE实现此目标

As per this question, I can use recursive CTE to achieve this

WITH r ( tablename, columnvalue, rankofcolumn, PATH ) AS
         (SELECT    tablename,
                    columnvalue,
                    rankofcolumn,
                    columnvalue
          FROM      test
          WHERE     rankofcolumn = 1
          UNION ALL
          SELECT    xx.tablename,
                    xx.columnvalue,
                    xx.rankofcolumn,
                    r.PATH || '->' || xx.columnvalue
          FROM      r
                    JOIN test xx
                        ON     xx.tablename = r.tablename
                           AND xx.rankofcolumn = r.rankofcolumn + 1)
SELECT    *
FROM      r;

但是我正在使用目前缺少此选项的WX2数据库.是否有SQL替代方法?

But I am using WX2 database which lacks this option at the moment. Is there a SQL alternative for this?

推荐答案

您可以对逐渐填充的表进行暴力破解.假设您的test表如下所示:

You could do the brute-force approach with a table that you gradually populate. Assuming your test table looks something like:

create table test (tablename varchar2(9), columnvalue varchar2(11), rankofcolumn number);

然后可以使用以下方法创建result表:

then the result table could be created with:

create table result (tablename varchar2(9), columnvalue varchar2(11), rankofcolumn number,
  path varchar2(50));

然后创建最低排名的结果条目:

Then create the result entries for the lowest rank:

insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn, t.columnvalue
from test t
where t.rankofcolumn = 1;

3 rows inserted.

并反复添加以现有最高等级为基础的行，并从test表中获取以下值(如果该tablename包含以下值):

And repeatedly add rows building on the highest existing rank, getting the following values (if there are any for that tablename) from the test table:

insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
  concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 2;

3 rows inserted.

insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
  concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 3;

2 rows inserted.

insert into result (tablename, columnvalue, rankofcolumn, path)
select t.tablename, t.columnvalue, t.rankofcolumn,
  concat(concat(r.path, '->'), t.columnvalue)
from test t
join result r
on r.tablename = t.tablename
and r.rankofcolumn = t.rankofcolumn - 1
where t.rankofcolumn = 4;

1 row inserted.

，并继续争取尽可能多的列数(即，任何表的最高rankofcolumn).您也许可以在WX2中按程序进行此操作，反复进行直到插入零行为止.但是您的声音听起来很有限.

And keep going for the maximum possible number of columns (i.e. highest rankofcolumn for any table). You may be able to do that procedurally in WX2, iterating until zero rows are inserted; but you've made it sound pretty limited.

所有这些迭代之后，表格现在包含:

After all those iterations the table now contains:

select * from result
order by tablename, rankofcolumn;

TABLENAME COLUMNVALUE RANKOFCOLUMN PATH                                             
--------- ----------- ------------ --------------------------------------------------
A         C1                     1 C1                                                
A         C2                     2 C1->C2                                            
A         C3                     3 C1->C2->C3                                        
A         C4                     4 C1->C2->C3->C4                                    
B         CX1                    1 CX1                                               
B         CX2                    2 CX1->CX2                                          
C         CY1                    1 CY1                                               
C         CY2                    2 CY1->CY2                                          
C         CY3                    3 CY1->CY2->CY3                                     

在Oracle中进行了测试，但尝试避免任何Oracle特有的问题；当然，可能需要对WX2进行调整.

Tested in Oracle but trying to avoid anything Oracle-specific; might need tweaking for WX2 of course.

这篇关于递归CTE的SQL替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！