趣题, 第8道来了。

lugionline · 发表于 2012-5-10 10:01

12123434343232121

lugionline · 发表于 2012-5-10 10:20

晕，还有更长的，就是从1234开始的那个,一共15个节点，其实上面这个形成一个换我应当想到可以另外加个点的

gyhgood · 发表于 2012-5-10 10:44

看到后面简直就是在看天书。。。。。。。不知所云

tree_new_bee · 发表于 2012-5-10 10:50

本帖最后由 tree_new_bee 于 2012-5-10 11:00 编辑

lugionline 发表于 2012-5-10 10:01
12123434343232121

那个画图软件不错，推荐个下载地址？

用的语句是SQLSERVER的？ with的语法与oracle几乎一样啊。
lugionline对递归语句用的很纯熟啊。

翻译成oracle：
with
A (n) As
(
      Select 1 from dual
      Union All
      Select n + 1 From A Where n < 4
)
,
V0 (S, L, C) As
(
      Select Cast(n As Varchar(2000)) S, n L, 1 C From A
      Union All
      Select S || Cast(n As Varchar(2000)), n, C + 1 From V0, A
      Where Abs(L - n) = 1 And C <= 4
)
,
V  As (Select S From V0 Where C = 4)
,
R(F,T,GV)  As
(
      Select VF.S F, VT.S T, VF.S || '->' || VT.S GV From V VF, V VT
      Where SubStr(VF.S, 2, Length(VT.S) - 1) = SubStr(VT.S, 1, Length(VT.S) - 1)
)
Select * From R Order By F;

lugionline · 发表于 2012-5-10 11:05

graphviz不错吧，呵呵
http://www.graphviz.org/

jixch · 发表于 2012-5-10 12:32

WITH n AS (
SELECT 1212 t,1 t1, 2 t2,1 t3, 2 t4 FROM dual UNION ALL
SELECT 1232 t,1 t1, 2 t2,3 t3, 2 t4 FROM dual UNION ALL
SELECT 1234 t,1 t1, 2 t2,3 t3, 4 t4 FROM dual UNION ALL
SELECT 2121 t,2 t1, 1 t2,2 t3, 1 t4 FROM dual UNION ALL
SELECT 2123 t,2 t1, 1 t2,2 t3, 3 t4 FROM dual UNION ALL
SELECT 2321 t,2 t1, 3 t2,2 t3, 1 t4 FROM dual UNION ALL
SELECT 2323 t,2 t1, 3 t2,2 t3, 3 t4 FROM dual UNION ALL
SELECT 2343 t,2 t1, 3 t2,4 t3, 3 t4 FROM dual UNION ALL
SELECT 3212 t,3 t1, 2 t2,1 t3, 2 t4 FROM dual UNION ALL
SELECT 3232 t,3 t1, 2 t2,3 t3, 2 t4 FROM dual UNION ALL
SELECT 3234 t,3 t1, 2 t2,3 t3, 4 t4 FROM dual UNION ALL
SELECT 3432 t,3 t1, 4 t2,3 t3, 2 t4 FROM dual UNION ALL
SELECT 3434 t,3 t1, 4 t2,3 t3, 4 t4 FROM dual UNION ALL
SELECT 4321 t,4 t1, 3 t2,2 t3, 1 t4 FROM dual UNION ALL
SELECT 4323 t,4 t1, 3 t2,2 t3, 3 t4 FROM dual UNION ALL
SELECT 4343 t,4 t1, 3 t2,4 t3, 3 t4 FROM dual
),n1 AS (
SELECT LEVEL lv,
   connect_by_root(n.t) root,
   sys_connect_by_path(n.t, '/') path1,
   sys_connect_by_path(n.t4, '/') path2
  FROM n
CONNECT BY nocycle(PRIOR t2 = t1
            AND PRIOR t3 = t2
            AND PRIOR t4 = t3) START WITH t=1212
)
SELECT * FROM n1
结果如下：

我红色框标出来的后面不是应该至少还可以叫 4343 么？
为什么结果没有呀？
各位高手帮我看下，看什么问题？
oracle版本：10.2.0.3

tree_new_bee · 发表于 2012-5-10 12:58

jixch 发表于 2012-5-10 12:32
WITH n AS (
SELECT 1212 t,1 t1, 2 t2,1 t3, 2 t4 FROM dual UNION ALL
SELECT 1232 t,1 t1, 2 t2,3 t3 ...

这个很古怪，我第一次写的时候也是跟你同样的错误现象。
后来把CONNECT BY nocycle(PRIOR t2 = t1
AND PRIOR t3 = t2
AND PRIOR t4 = t3)
改成了CONNECT BY nocycle(substr(prior t, 2,3) = substr(t,1,3) 就正常了。

甚至改成(PRIOR t2||PRIOR t3||PRIOR t4 = t1||t2||t3 也不行。

三个prior与nocycle之间不知发生了什么，也不知是不是bug.

tree_new_bee · 发表于 2012-5-10 13:10

或者在你的三个and条件后面加上and prior t1 <> prior t2, 也能得到正确结果。

总觉得怪怪的，不知道这里的nocycle是怎么判断的。

jixch · 发表于 2012-5-10 13:25

tree_new_bee 发表于 2012-5-10 12:58
这个很古怪，我第一次写的时候也是跟你同样的错误现象。
后来把CONNECT BY nocycle(PRIOR t2 = t1
...

SELECT LEVEL lv,
   connect_by_root(n.t) root,
   sys_connect_by_path(n.t, '/') path1,
   sys_connect_by_path(n.t4, '/') path2
  FROM n
CONNECT BY nocycle(substr(prior t, 2,3) = substr(t,1,3)) --START WITH t=1212
我把START WITH t=1212注释掉就是所有的
但是如果我加上不注释的话，就只能查到一条记录....
BUG？....

newkid · 发表于 2012-5-10 21:57

lugionline 发表于 2012-5-10 10:01
12123434343232121

肉眼看出来的？不错嘛只差一节。

[精华] 趣题, 第8道来了。