DB2:搜索深度优先:(语法)错误

Question

几天前，我在一台服务器上安装了DB2LUW(11.5(。现在我想做一些递归SQL(递归公共表表达式(：

让我展示一下我是如何设置的：

drop table relations;
create table relations (id int, parent int);
insert into relations values(0,NULL);
insert into relations values(1,0);
insert into relations values(2,1);
insert into relations values(3,1);
insert into relations values(4,3);
insert into relations values(5,0);
insert into relations values(6,5);
insert into relations values(7,5);
insert into relations values(8,6);
insert into relations values(9,7);
insert into relations values(10,0);
insert into relations values(11,1);
commit;

现在我想看看表中的层次结构。所以我尝试了以下方法：

with recur(id, parent, level) as
(
select rel.id id, rel.parent parent, 0 level from relations rel where rel.id=0
union all
select rel.id, rel.parent, rec.level+1 from recur rec, relations rel where rec.id=rel.parent
and rec.level<10
)
select id, lpad(parent, level*2, ' ') from recur;

这给了我：

ID          PARENT                                                                                                         
----------- ------------------
0 -                                                                                                              
1  0                                                                                                             
5  0                                                                                                             
10  0                                                                                                             
2    1                                                                                                           
3    1                                                                                                           
11    1                                                                                                           
6    5                                                                                                           
7    5                                                                                                           
4      3                                                                                                         
8      6                                                                                                         
9      7                                                                                                         

这是(对我来说(：；"搜索广度优先"；我想看到的是"；"搜索深度优先"；

所以我做了这个：

with recur(id, parent, level) as
(
select rel.id id, rel.parent parent, 0 level from relations rel where rel.id=0
union all
select rel.id, rel.parent, rec.level+1 from recur rec, relations rel where rec.id=rel.parent 
and rec.level<10 
) 
search depth first by parent set ord
select id, lpad(parent, level*2, ' ') parent from recur order by ord;

但这给了我：

SQL0104N  An unexpected token "search depth first by parent set ord sel" was 
found following "t and rec.level<10 )".  Expected tokens may include:  
"<values>".  SQLSTATE=42601

现在不知道该怎么解决。我(想我(已经尝试了很多可能的解决方案。但都没有奏效。我开始相信DB2LUW(11.5(不知道SearchDepthFirst。或者必须进行一些设置以使DB2知道"；SDF"；可能

我向大家提出的问题是：如何解决这个问题？如何让Search Depth First工作？

从积极的方面来说。。。。跟随就像一个符咒。。。。但这不是我想知道的：-(

select id, lpad(parent, level*2, ' ') parent, level
from   relations 
start with id=0
connect by prior id=parent;
ID          PARENT     LEVEL
----------- ---------- -----------
0 -                    1
1    0                 2
2      1               3
3      1               3
4        3             4
11      1               3
5    0                 2
6      5               3
8        6             4
7      5               3
9        7             4
10    0                 2

这很有魅力，但我不得不在数据库中进行切换(并重新启动(：

db2set DB2_COMPATIBILITY_VECTOR=08

Answer 1

您的问题是关于按特定顺序显示行，而不是关于按特定顺序搜索。

通过组合一个符合您需要的排序列，您可以在所需的排序中显示行。

例如：

with
n (id, parent, lvl, ordering) as (
select id, parent, 1, lpad(id, 3, '0') || lpad('', 30, ' ')
from relations 
where parent is null
union all
select r.id, r.parent, n.lvl + 1, trim(n.ordering) || '/' || lpad(r.id, 3, '0')
from n, relations r where r.parent = n.id
)
select id, lpad(parent, lvl * 2, ' ') as parent, lvl
from n
order by ordering;

结果：

ID  PARENT    LVL 
--- --------- --- 
0             1   
1      0      2   
2        1    3   
3        1    3   
4          3  4   
11       1    3   
5      0      2   
6        5    3   
8          6  4   
7        5    3   
9          7  4   
10     0      2   

请参阅db<gt；不停摆弄