递归CTE的SQL替换

As per this question, I can use recursive CTE to achieve thisWITH 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.columnvaluefrom test twhere 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 tjoin result ron r.tablename = t.tablenameand r.rankofcolumn = t.rankofcolumn - 1where 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 tjoin result ron r.tablename = t.tablenameand r.rankofcolumn = t.rankofcolumn - 1where 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 tjoin result ron r.tablename = t.tablenameand r.rankofcolumn = t.rankofcolumn - 1where 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 resultorder by tablename, rankofcolumn;TABLENAME COLUMNVALUE RANKOFCOLUMN PATH--------- ----------- ------------ --------------------------------------------------A C1 1 C1A C2 2 C1->C2A C3 3 C1->C2->C3A C4 4 C1->C2->C3->C4B CX1 1 CX1B CX2 2 CX1->CX2C CY1 1 CY1C CY2 2 CY1->CY2C 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替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！