sys_connect_by_path 进行列连接的疑问

skyspark · 发表于 2007-7-28 21:16

从网上看到的例子有点疑惑
[PHP]
SQL> DESC TEST
Name Type       Nullable Default Comments
---- ------------ -------- ------- --------
A NUMBER    Y
B VARCHAR2(20) Y

SQL> select * from test;

      A B
---------- --------------------
      1 a
      1 b
      1 c
      1 d
      1 e
      2 a
      2 b
      3 a
      3 v
      3 d

想要的结果:
      A RESULT
---------- --------------------------------------------------------------------------------
      1 e;d;c;b;a
      2 b;a
      3 v;d;a

有以下两种方法：
方法1：
select a,substr(max(sys_connect_by_path(b,';')),2) result
from
(select a,b,
      row_number() over(order by a,b desc) rn,
      row_number() over(order by a,b desc)-1 rn1,
      --+dense_rank() over(order by a)) rn,
      max(b) over(partition by a) bs
from test)
start with b=bs
connect by rn1 = prior rn and a = prior a
group by a;

方法2：
select a,max(sys_connect_by_path(b,';')) result
from
(select a,b,
      (row_number() over(order by a,b desc)
      --row_number()-1 over(order by a,b desc) rn1
      +dense_rank() over(order by a)) rn,
      max(b) over(partition by a) bs
from test)
start with b=bs
connect by rn-1 = prior rn
group by a;
[/PHP]

对于方法2中 rn-1 = prior rn 不是很理解请指点顺便我看他说是通过构造树来做到的
问题：1、 prior的用法（自己在网上搜索了看了不是很理解）
2、在上面例子中是如何构造树的？

skyspark · 发表于 2007-7-28 21:57

[PHP]
自己把里面的运行了下
SQL> select a,b,(row_number() over(order by a,b desc)+dense_rank() over(order by a)) rn from test;

      A B                         RN
---------- -------------------- ----------
      1 e                            2
      1 d                            3
      1 c                            4
      1 b                            5
      1 a                            6
      2 b                            8
      2 a                            9
      3 v                         11
      3 d                         12
      3 a                         13

10 rows selected
[/PHP]

再参照http://www.itpub.net/716901.html这篇文章总算搞懂了

hanjs · 发表于 2007-7-28 22:43

只有自己动手做才会提高，支持！