select c.id,c.pn,a.name from(select d.id,max(sys_connect_by_path(d.pn,',')) as pn
from(
select b.id,b.pn,row_number()over(order by b.id,b.pn)+dense_rank()over(order by b.id) as rn,
min(pn)over(partition by b.id) as mpn from b) d
start with d.pn=d.mpn
connect by prior d.rn=d.rn-1
group by id) c,a
where c.id=a.id
from(
select b.id,b.pn,row_number()over(order by b.id,b.pn)+dense_rank()over(order by b.id) as rn,
min(pn)over(partition by b.id) as mpn from b) d
start with d.pn=d.mpn
connect by prior d.rn=d.rn-1
group by id) c,a
where c.id=a.id
解决方案 »
- 挥泪求助,能人进
- oracle-12154 TNS:cound not resolve the connect identifier specified
- 关于一个存储过程的问题,求助。
- 请教一个ORACLE删除时判断外键是否有数据的问题
- 编译db-db-4.8.30.NC.tar.gz遇到unexpected EOF while looking for matching
- 抱怨!抱怨!抱怨!oracle要自己手写视图!!!!
- 怪异!急!急!急!关于SQL*Plus工作单与Oracle Enterprise Manager Console之间的一个怪异的问题?
- 我想实现不同数据库下两张相同表的数据同步该怎么做
- 用oracle9i新建了一个数据库,怎么登陆到management server时,看不到这个新的数据库呀?
- oracle的导出导入问题(参数的设置)?
- 求一SQL语句
- 如何设置字段的显示长段
谁有适合ora8.01的方法;,谢谢。
顶贴有分
以前看过一位叫“duanzilin (寻) ”的方法,很好,在这里转发给你SQL> select no,q from test
2 /NO Q
---------- ------------------------------
001 n1
001 n2
001 n3
001 n4
001 n5
002 m1
003 t1
003 t2
003 t3
003 t4
003 t5
003 t612 rows selected最后要得到类似于如下的结果:
001 n1;n2;n3;n4;n5
002 m1
003 t1;t2;t3;t4;t5;t6 SQL> select no,q,rn,lead(rn) over(partition by no order by rn) rn1
2 from (select no,q,row_number() over(order by no,q desc) rn from test)
3 /NO Q RN RN1
---------- ------------------------------ ---------- ----------
001 n5 1 2
001 n4 2 3
001 n3 3 4
001 n2 4 5
001 n1 5
002 m1 6
003 t6 7 8
003 t5 8 9
003 t4 9 10
003 t3 10 11
003 t2 11 12
003 t1 12 12 rows selected有了这个树型的结构,接下来的事就好办了,只要取出拥有全路径的那个path,问题就解决了,先看no=‘001’的分组:
select no,sys_connect_by_path(q,';') result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = '001' and rn1 is null connect by rn1 = prior rn
SQL>
6 /NO RESULT
---------- --------------------------------------------------------------------------------
001 ;n1
001 ;n1;n2
001 ;n1;n2;n3
001 ;n1;n2;n3;n4
001 ;n1;n2;n3;n4;n5上面结果的最后1条就是我们要得结果了
要得到每组的结果,可以下面这样select t.*,
(
select max(sys_connect_by_path(q,';')) result from
(select no,q,rn,lead(rn) over(partition by no order by rn) rn1
from (select no,q,row_number() over(order by no,q desc) rn from test)
)
start with no = t.no and rn1 is null connect by rn1 = prior rn
) value
from (select distinct no from test) tSQL>
10 /NO VALUE
---------- --------------------------------------------------------------------------------
001 ;n1;n2;n3;n4;n5
002 ;m1
003 ;t1;t2;t3;t4;t5;t6
from(
select b.id,b.pn,row_number()over(order by b.id,b.pn)+dense_rank()over(order by b.id) as rn,
min(pn)over(partition by b.id) as mpn from b) d
start with d.pn=d.mpn
connect by prior d.rn=d.rn-1
group by id) c,a
where c.id=a.id;
这是在ORACLE 9I上运行的,工具是PLSQL Developer。
ID NAME PN
--------- ---------- ---------------------
1 a ,a1,a2,a3
2 b ,b1,b2
3 c ,c1,c2,c3
这是运行结果
select b.id,b.pn,row_number()over(order by b.id,b.pn)+dense_rank()over(order by b.id) as rn,min(pn)over(partition by b.id) as mpn from b 是d表,这里的函数功能我明白,只是为什么这么写没明白。
select d.id,max(sys_connect_by_path(d.pn,',')) as pn
from d
start with d.pn=d.mpn
connect by prior d.rn=d.rn-1
group by id是 c表,sys_connect_by_path(d.pn,',')这个函数为什么这么用,我就一点都不明白了。
select c.id,a.name ,c.pn from c,a
where c.id=a.id;只有最外面这个我看懂了.