最近看了一篇枫$叶的文章http://blog.csdn.net/mantisXF/archive/2007/07/19/1699067.aspx
深受启发,今天测试了一下connect by的使用,遇到一个问题,请协助解决create table abc_1
(a varchar(10),
b number)
insert into abc_1
select 'in',1 from dual
union all
select 'in',45 from dual
union all
select 'in',35 from dual
union all
select 'in',25 from dual
union all
select 'in',15 from dual
union all
select 'out',10 from dual
union all
select 'out',11 from dual
union all
select 'out',12 from dual
union all
select 'out',13 from dual
union all
select 'out',14 from dual
select a,
max(substr((sys_connect_by_path(b, ',')), 2)) unite
from (select a,
b,
rownum rnum,
row_number() over(partition by a order by a) rn
from abc_1)
start with rn = 1
connect by prior rnum = rnum - 1
group by a
输出为:
A UNITE
---------- --------------------------------------------------------------------------------
in 1,45,35,25,15
out 10,11,12,13,14
一切正常但将in对应的1修改为50(许多数都这样)
输出为:
A UNITE
---------- --------------------------------------------------------------------------------
in 50,45,35,25,15
out 50,45,35,25,15,10,11,12,13,14请问造成这种错误的原因是什么?为什么数据的不同会影响结果呢?
深受启发,今天测试了一下connect by的使用,遇到一个问题,请协助解决create table abc_1
(a varchar(10),
b number)
insert into abc_1
select 'in',1 from dual
union all
select 'in',45 from dual
union all
select 'in',35 from dual
union all
select 'in',25 from dual
union all
select 'in',15 from dual
union all
select 'out',10 from dual
union all
select 'out',11 from dual
union all
select 'out',12 from dual
union all
select 'out',13 from dual
union all
select 'out',14 from dual
select a,
max(substr((sys_connect_by_path(b, ',')), 2)) unite
from (select a,
b,
rownum rnum,
row_number() over(partition by a order by a) rn
from abc_1)
start with rn = 1
connect by prior rnum = rnum - 1
group by a
输出为:
A UNITE
---------- --------------------------------------------------------------------------------
in 1,45,35,25,15
out 10,11,12,13,14
一切正常但将in对应的1修改为50(许多数都这样)
输出为:
A UNITE
---------- --------------------------------------------------------------------------------
in 50,45,35,25,15
out 50,45,35,25,15,10,11,12,13,14请问造成这种错误的原因是什么?为什么数据的不同会影响结果呢?
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as SYS
SQL>
SQL> select a,
2 sys_connect_by_path(b, ',') unite,
3 level lev
4 from (select a,
5 b,
6 rownum rnum,
7 row_number() over(partition by a order by a) rn
8 from abc_1)
9 start with rn = 1
10 connect by prior rnum = rnum - 1;A UNITE LEV
---------- -------------------------------------------------------------------------------- ----------
in ,1 1
in ,1,45 2
in ,1,45,35 3
in ,1,45,35,25 4
in ,1,45,35,25,15 5
out ,1,45,35,25,15,10 6
out ,1,45,35,25,15,10,11 7
out ,1,45,35,25,15,10,11,12 8
out ,1,45,35,25,15,10,11,12,13 9
out ,1,45,35,25,15,10,11,12,13,14 10
out ,10 1
out ,10,11 2
out ,10,11,12 3
out ,10,11,12,13 4
out ,10,11,12,13,14 515 rows selectedSQL>
SQL> select a,
2 sys_connect_by_path(b, ',') unite,
3 level lev
4 from (select a,
5 b,
6 rownum rnum,
7 row_number() over(partition by a order by a) rn
8 from abc_2)
9 start with rn = 1
10 connect by prior rnum = rnum - 1;A UNITE LEV
---------- -------------------------------------------------------------------------------- ----------
in ,50 1
in ,50,45 2
in ,50,45,35 3
in ,50,45,35,25 4
in ,50,45,35,25,15 5
out ,50,45,35,25,15,10 6
out ,50,45,35,25,15,10,11 7
out ,50,45,35,25,15,10,11,12 8
out ,50,45,35,25,15,10,11,12,13 9
out ,50,45,35,25,15,10,11,12,13,14 10
out ,10 1
out ,10,11 2
out ,10,11,12 3
out ,10,11,12,13 4
out ,10,11,12,13,14 515 rows selectedSQL> 对于上面的SQL,当你用了max(substr(**,2))后,就会取分组后值最大的那条记录.而为什么in对应的1没有变50前的SQL查询是对的,in对应的1改成50后的SQL查询是不对的呢?
因为对于前面SQL查询来说,当你用substr后就会把前面的","号去掉,然后当你用max函数时就会取UNITE字段值最大的记录"10,11,12,13,14";而对于后面的SQL查询来说相应UNITE字段值最大的记录就是"50,45,35,25,15,10,11,12,13,14",具体为什么它是取按这种方式取最大的就不用我说了吧?所有对于你的SQL写法是不正确的,rownum和row_number混用了，当你以后这样写就会发现问题的．所有最好的方式是都用row_number，如果需要分组来connect by的话，那么还要加上一个唯一键值(最好是主键值)，那么就不会有什么大问题了．具体的SQL查询可以参考如下语句：
SQL>
SQL> select distinct
2 zz.a,
3 first_value(unite) over(partition by a order by lev desc) as b
4 from (
5 select a,
6 substr(sys_connect_by_path(b, ','), 2) unite,
7 level lev
8 from (select a,
9 b,
10 a||(row_number() over(partition by a order by a)-1) as rn_front,
11 a||row_number() over(partition by a order by a) as rn_behind
12 from abc_1)
13 connect by prior rn_front = rn_behind
14 )zz;A B
---------- --------------------------------------------------------------------------------
in 15,25,35,45,1
out 14,13,12,11,10Good luck to you!
看了你的回复,收获颇多,可否要一个你的邮箱?
有什么问题,我直接邮件联系你,也比较方便.
可以的话给我发个邮件就行
[email protected]
我也刚毕业一年,不多才23岁,以后多交流~~PS:白低调了牛人,我看过你的Blog和你回答的问题,都把你当成偶像了~~Best regards！