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 这三条
在规则非常不确定的情况下,严格一点的判别连号实现: 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
还是这样显示的 title 我的身体偷偷出轨 p 3,3,3,3,3,gm00007,gm00007,gm00007,gm00007,gm00007- gm00008,gm00008,gm00008,gm00008,gm00008但我库中只有3,gm0007,gm0008 这三条
这改
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'
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
这三条
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
title
我的身体偷偷出轨
p
3,3,3,3,3,gm00007,gm00007,gm00007,gm00007,gm00007- gm00008,gm00008,gm00008,gm00008,gm00008但我库中只有3,gm0007,gm0008
这三条
and b.checkrecno=x.recno
这个有问题