请教start with ......connect by prior .....用法

上一篇 / 下一篇 2007-11-09 10:26:13 / 个人分类：Oracle资料与实践

查看( 439 ) / 评论( 15 )

在doc里面看了start with ，但是没有完全明白其含义，哪位大侠路过给解释一下，最好能给一个形象一点的例子。

TAG:

oracle_worker发布于2007-11-09 10:35:25: Start with 开始位置，connect by prior 子节点=父节点；
必须使用prior引用父行
sys_connect_by_path(userid, ',')userid, 之间用什么符号分割
With x As (
SELECT 'test_1' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_1' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_4' NAME, 'test1' userid FROM dual )
select Name, ltrim(max(sys_connect_by_path(userid, ',')), ',') userid
  from (select Name,
            userid,
            row_number() over(partition by Name order by userid) rn
      from x)
start with rn = 1
connect by rn - 1 = prior rn
   And Name = Prior Name
group by Name
order by Name;

foxmile 发布于2007-11-09 10:52:01: And Name = Prior Name
我注释之后并没有出现不同的结果，请告知这里这个条件是什么作用。谢谢！

oracle_worker发布于2007-11-09 10:55:35: 其实起主要作用的是rn - 1 = prior rn ，
Name = Prior Name可要可不要。

foxmile 发布于2007-11-09 20:15:47: OCP doc就是oracle认证的英文文档，里面的知识很全。
QUOTE:
最初由 jack198409 发布
[B]請問哈兄弟門OCP doc的全稱是什么啊？
我也好學習哈啊！ [/B]

foxmile 发布于2007-11-09 20:16:09: 多谢啦。我已经在看了。
QUOTE:
最初由 zhouwf0726 发布
[B]http://www.itpub.net/620427,1.html

[/B]

bluelliant发布于2007-11-10 09:03:23: QUOTE:
最初由 oracle_worker 发布
[B]Start with 开始位置，connect by prior 子节点=父节点；
必须使用prior引用父行
sys_connect_by_path(userid, ',')userid, 之间用什么符号分割
With x As (
SELECT 'test_1' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_1' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_2' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test1' userid FROM dual UNION ALL
SELECT 'test_3' NAME, 'test2' userid FROM dual UNION ALL
SELECT 'test_4' NAME, 'test1' userid FROM dual )
select Name, ltrim(max(sys_connect_by_path(userid, ',')), ',') userid
  from (select Name,
            userid,
            row_number() over(partition by Name order by userid) rn
      from x)
start with rn = 1
connect by rn - 1 = prior rn
   And Name = Prior Name
group by Name
order by Name; [/B]
Note: In a hierarchical query, do not specify either ORDER BY or
GROUP BY, as they will destroy the hierarchical order of the CONNECT
BY results. If you want to order rows of siblings of the same parent,
then use the ORDER SIBLINGS BY clause.

oracle_worker发布于2007-11-10 11:07:40: QUOTE:
最初由 bluelliant 发布
[B]

Note: In a hierarchical query, do not specify either ORDER BY or
GROUP BY, as they will destroy the hierarchical order of the CONNECT
BY results. If you want to order rows of siblings of the same parent,
then use the ORDER SIBLINGS BY clause. [/B]
学习了，你能举个列子吗？谢谢！