yaozw_mountain(山林)yaozw_mountain(山林)
我是那个阿华,那个贴不能回复了,
SELECT title, REPLACE(substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ),'-,','-') p
FROM(
SELECT title,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY barcode) rr,
sum(length( decode(ld,'01',barcode||'-',barcode))+1)over(PARTITION BY title ORDER BY barcode) ls,
decode(ld,'01',barcode||'-',barcode) barcode, title,ld
FROM(
SELECT a.title , b.barcode ,
decode( lag ( to_number(substr( b.barcode,3)) )over(PARTITION BY a.title ORDER BY b.barcode) +1,to_number(substr( b.barcode,3)),'1','0') ||
decode( lead( to_number(substr( b.barcode,3)) )over(PARTITION BY a.title ORDER BY b.barcode) -1,to_number(substr( b.barcode,3)),'1','0') ld
from biblios a, holding b
where a.bookrecno=b.bookrecno and rtrim( substr( b.barcode,3),'0123456789') IS NULL
--and b.barcode is not null
--and a.title=''
)a
WHERE ld <> '11'
) a
WHERE ld IN( '00' ,'10')
START WITH a.rr=1
CONNECT BY ls< 1000 and a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
)a
GROUP BY title
这样显示结果为空:
title p
就这样的,什么也没得,
问题点数：0、回复次数：10Top
1 楼yaozw_mountain（山林）回复于 2006-12-01 14:23:09 得分 0
b.barcode是什么格式？前后有没有空格，是不是定长，长度有多长，
多给几个例子
把你的标题改了
Top
2 楼andyljf（阿华）回复于 2006-12-01 14:41:14 得分 0
barcode是图书的条码号不是定长的,长度一般不会超过20位有可能是全数字,也有可能前面是字母来的Top
3 楼yaozw_mountain（山林）回复于 2006-12-01 15:01:30 得分 0
barcode典型的样例呢？
如果能保证后几位为数字，这里用4
这改
substr( b.barcode,3)
为
substr( b.barcode, -4)
否则用
substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)
去替换 substr( b.barcode,3)
这么做的目的取出数字序号
严格一点的判别连号：
'gm0002' 的上一号:'gm'||'0001' 下一号:'gm'||'0003'
Top
4 楼andyljf（阿华）回复于 2006-12-01 15:21:56 得分 0
SELECT title, REPLACE(substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ),'-,','-') p
FROM(
SELECT title,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY barcode) rr,
sum(length( decode(ld,'01',barcode||'-',barcode))+1)over(PARTITION BY title ORDER BY barcode) ls,
decode(ld,'01',barcode||'-',barcode) barcode, title,ld
FROM(
SELECT a.title , b.barcode ,
decode( lag ( to_number(substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)) )over(PARTITION BY a.title ORDER BY b.barcode) +1,to_number(substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)),'1','0') ||
decode( lead( to_number(substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)) )over(PARTITION BY a.title ORDER BY b.barcode) -1,to_number(substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)),'1','0') ld
from biblios a, holding b,book_check x
where a.bookrecno=b.bookrecno and rtrim( substr( b.barcode,3),'0123456789') IS NULL and b.checkrecno=x.recno
--and b.barcode is not null
--and a.title=''
)a
WHERE ld <> '11'
) a
WHERE ld IN( '00' ,'10')
START WITH a.rr=1
CONNECT BY ls< 1000 and a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
)a
GROUP BY title
这样显示的
title
我的身体偷偷出轨
p
3,3,3,3,3,gm00007,gm00007,gm00007,gm00007,gm00007- gm00008,gm00008,gm00008,gm00008,gm00008
但我库中只有3,gm0007,gm0008
这三条Top
5 楼yaozw_mountain（山林）回复于 2006-12-01 15:22:51 得分 0
在规则非常不确定的情况下，严格一点的判别连号实现：
decode( lag ( b.barcode)over(PARTITION BY a.title ORDER BY b.barcode) ,
rtrim( b.barcode,'0123456789') || lpad(to_char( to_number(
substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)
) -1 ), length(b.barcode) - nvl(length( rtrim( b.barcode,'0123456789') ),0), '0')
,'1','0') ||
decode( lead( b.barcode)over(PARTITION BY a.title ORDER BY b.barcode) ,
rtrim( b.barcode,'0123456789') || lpad(to_char( to_number(
substr( b.barcode, nvl(length( rtrim( b.barcode,'0123456789') ),0)+1)
) +1 ), length(b.barcode) - nvl(length( rtrim( b.barcode,'0123456789') ),0), '0')
,'1','0') ld
这样就不须要使用异常过滤了：
-- and rtrim( substr( b.barcode,3),'0123456789') IS NULL
Top
6 楼andyljf（阿华）回复于 2006-12-01 15:26:05 得分 0
还是这样显示的
title
我的身体偷偷出轨
p
3,3,3,3,3,gm00007,gm00007,gm00007,gm00007,gm00007- gm00008,gm00008,gm00008,gm00008,gm00008
但我库中只有3,gm0007,gm0008
这三条
Top
7 楼yaozw_mountain（山林）回复于 2006-12-01 15:28:07 得分 0
,book_check x
and b.checkrecno=x.recno
这个有问题Top
8 楼andyljf（阿华）回复于 2006-12-01 15:36:11 得分 0
哦,非常谢谢你,你教会了我很多,我去结那个贴了,谢谢你了Top
9 楼andyljf（阿华）回复于 2006-12-01 15:37:00 得分 0
你有QQ吗,能加你吗?以后好向你学习一下Top
10 楼andyljf（阿华）回复于 2006-12-01 15:38:09 得分 0
怎么结贴,怎么给分?是选中你回答的,在后面输入分数,按什么?Top
