SELECT title, MAX (SUBSTR (RESULT, 2))
FROM (SELECT title, barcode, SYS_CONNECT_BY_PATH (barcode, ',') RESULT
FROM (SELECT title, barcode, RN,
LEAD (RN) OVER (PARTITION BY title ORDER BY RN)
RN1
FROM (SELECT title, barcode,
ROW_NUMBER () OVER (ORDER BY title) RN
FROM tablename))
START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
ORDER BY RN)
GROUP BY title
FROM (SELECT title, barcode, SYS_CONNECT_BY_PATH (barcode, ',') RESULT
FROM (SELECT title, barcode, RN,
LEAD (RN) OVER (PARTITION BY title ORDER BY RN)
RN1
FROM (SELECT title, barcode,
ROW_NUMBER () OVER (ORDER BY title) RN
FROM tablename))
START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
ORDER BY RN)
GROUP BY title
FROM (SELECT b.title, a.barcode, SYS_CONNECT_BY_PATH (a.barcode, ',') RESULT
FROM (SELECT b.title, a.barcode, RN,
LEAD (RN) OVER (PARTITION BY b.title ORDER BY RN)
RN1
FROM (SELECT b.title, a.barcode,
ROW_NUMBER () OVER (ORDER BY b.title) RN
FROM holding a,biblios b))
START WITH RN1 IS NULL
CONNECT BY RN1 = PRIOR RN
ORDER BY RN)
GROUP BY b.title
我这两个字段在两个表就出现b.title无效,大虾
"ORA-01489:字符串连接的结果过长".
gm0001 <<JAVA程序设计>>
gm0002 <<JAVA程序设计>>
gm0003 <<JAVA程序设计>>
gm0004 <<数据结构>>
gm0005 <<数据结构>>
gm0008 <<JAVA程序设计>>
gm0009 <<数据结构>>
gm0006 <<JAVA程序设计>>
gm0007 <<数据结构>>
SELECT title, substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
FROM(
SELECT title ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY title ORDER BY t.barcode) rr, t.* from a_t1 t
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1 AND a.title = PRIOR a.title
)a
GROUP BY title
不过还是定义一个通用字符串连接组函数来的方便,也可通用
FROM(
SELECT title ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from title a, barcode b
where a.recno = b.bookrecno
-- and ....其他条件
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1 AND a.title = PRIOR a.title
)a
GROUP BY title
SELECT b.state as state,title, substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
出现了b.state无效的标识符,这是怎么回事
SELECT title,state , substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
FROM(
SELECT title,state ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title,b.state ORDER BY b.barcode) rr,
a.title , b.barcode, b.state
from title a, barcode b
where a.recno = b.bookrecno
-- and ....其他条件
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title AND a.state = PRIOR a.state)a
GROUP BY title, state
SELECT title ,substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
FROM(
SELECT state,title ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title,b.state ORDER BY b.barcode) rr,
b.state,a.title , b.barcode
from biblios a, holding b
where a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1 AND a.title = PRIOR a.title AND a.state = PRIOR a.state
)a
group by title
gm00007
gm00008
3
应该显示的结果是title substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ) MAX(ll)
我的身体偷偷出轨 gm00007-08 ?
我的身体偷偷出轨 3 ?但显示的结果是
title substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ) MAX(ll)
我的身体偷偷出轨 1,2,3,4,5 5
FROM(
SELECT title ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.recno and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
)a
group by title这样显示的结果是:title substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ) MAX(ll)
我的身体偷偷出轨 gm00004 1可是gm00004这条记录的title是<<情人节的五个瞬间>>,
不过个人建议你去自己写一个过程看看自己能不能实现sys_connect_by_path的类似功能
CONNECT BY a.rr = PRIOR a.rr +1
==> AND a.title = PRIOR a.title AND a.state = PRIOR a.state)a
from biblios a, holding b
where a.title='我的身体偷偷出轨'
) a
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.recno and a.title='我的身体偷偷出轨'
输出结果字段和内容是什么?
1 我的身体偷偷出轨 gm00004
和
select * from holding;
内容分别是什么
bookrecno 相关书目记录号 NUMBER(10) holding
checkrecno 相关验收记录号 NUMBER(10) holding
state 馆藏状态,编目=1,在馆=2,借出=3,丢失=4,剔除=5,交换=6,赠送=7,装订=8,其它=9 NUMBER(1) holding
barcode 馆藏标识,条码号 NVARCHAR2(20) holding
callno 索书号 NVARCHAR2(100) holding
orglib 文献所有馆 NVARCHAR2(20) holding
orglocal 文献所有馆典藏位置 NVARCHAR2(20) holding
curlib 文献所在馆 NVARCHAR2(20) holding
curlocal 文献所在馆典藏位置 NVARCHAR2(20) holding
cirtype 文献流通类型代码,见p_bctype NVARCHAR2(20) holding
regdate 入档日期(审校交送日期) DATE holding
indate 馆藏产生日期 DATE holding
singleprice 单册价格 NUMBER(6,2) holding
totalprice 整套价格 NUMBER(6,2) holding
vendorno 书商代码 NVARCHAR2(20) holding
budgetno 经费来源,budgetno NVARCHAR2(10) holding
booksource 文献来源 NVARCHAR2(10) holding
mediatype 介质类型,区别到馆文献的介质类型,光盘、磁带等 NVARCHAR2(20) holding
bindinfo 装帧形式 NVARCHAR2(20) holding
regno 登录号 NVARCHAR2(20) holding
volnum 卷册数量 NUMBER(6) holding
volinfo 卷册信息 NVARCHAR2(100) holding
memoinfo 备注信息 NVARCHAR2(100) holding
countsign 清点标志 NVARCHAR2(10) holding
这是holding结构
marccontent marc数据内容 LONG biblios
bookstate 编目标志,直接接收=0,原编=1,接收书商=2,读者自荐=3,z3950新增=4,z3950合并=5,middles新增=6,middles合并=7, NUMBER(1) biblios
marcformat 书目格式,中文=CNMRAC,西文=USMARC NVARCHAR2(10) biblios
finish 书目数据完整标识,不完整=1,完整=9 NUMBER(1) biblios
booktype 资料类型,区别资料的属性,如book=1,series=2,media=3 NUMBER(1) biblios
booksign 是否附件,1=电子附件,2=纸张附件 NUMBER(1) biblios
createman 书目创建人员 NVARCHAR2(20) biblios
createtime 书目创建时间 TIMESTAMP biblios
catalogman 书目编目人员 NVARCHAR2(20) biblios
catalogtime 书目编目时间 TIMESTAMP biblios
checkman 书目审校人员 NVARCHAR2(20) biblios
checktime 书目审校时间 TIMESTAMP biblios
address 出版地 NVARCHAR2(100) biblios
attachment 附件 NVARCHAR2(100) biblios
author 著者 NVARCHAR2(200) biblios
classno 分类号 NVARCHAR2(100) biblios
controlno 控制号 NVARCHAR2(100) biblios
pubdate 出版日期 NVARCHAR2(100) biblios
isbn ISBN代码,国际标准书号 NVARCHAR2(100) biblios
issn ISSN代码,国际标准刊号 NVARCHAR2(100) biblios
language 语种 NVARCHAR2(100) biblios
orderno 订购号 NVARCHAR2(100) biblios
orderfor 国外订购号 NVARCHAR2(100) biblios
page 页码 NVARCHAR2(100) biblios
price 价格 NVARCHAR2(100) biblios
publisher 出版社 NVARCHAR2(100) biblios
booksize 尺寸 NVARCHAR2(100) biblios
title 题名 NVARCHAR2(300) biblios
type 资料类型 NVARCHAR2(100) biblios
unionno 统一书号 NVARCHAR2(100) biblios
edition 版次 NVARCHAR2(100) biblios
notes 附注内容 NVARCHAR2(2000) biblios
subject 主题 NVARCHAR2(500) biblios
summary 提要及文摘 NVARCHAR2(2000) biblios
frequency 出版频率 NVARCHAR2(60) biblios
这是biblios结构
recno NUMBER(10) not null,
bookrecno NUMBER(10),
checkrecno NUMBER(10),
state NUMBER(2),
barcode NVARCHAR2(20),
callno NVARCHAR2(100),
orglib NVARCHAR2(20),
orglocal NVARCHAR2(20),
curlib NVARCHAR2(20),
curlocal NVARCHAR2(20),
cirtype NVARCHAR2(20),
regdate DATE,
indate DATE default SYSDATE,
singleprice NUMBER(8,2),
totalprice NUMBER(8,2),
vendorno NVARCHAR2(20),
budgetno NVARCHAR2(10),
booksource NVARCHAR2(10),
mediatype NVARCHAR2(20),
bindinfo NVARCHAR2(20),
regno NVARCHAR2(20),
volnum NUMBER(6),
volinfo NVARCHAR2(100),
memoinfo NVARCHAR2(100),
countsign NVARCHAR2(10),
bindrecno NUMBER(10),
constraint PK_holding primary key (recno),
constraint FK_HOLDING_RESHOLD_BIBLIOS foreign key (bookrecno)
references biblios (bookrecno),
constraint FK_HOLDING_RESHOLD_BOOK_CHE foreign key (checkrecno)
references book_check (recno)
)这是建holding表
references biblios (bookrecno),应该用
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.bookrecno and a.title='我的身体偷偷出轨'
SELECT title,state , substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
FROM(
SELECT title,state ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title,b.state ORDER BY b.barcode) rr,
a.title , b.barcode, b.state
from biblios a, holding b
where a.bookrecno=b.bookrecno
-- and ....其他条件
-- and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title AND a.state = PRIOR a.state)a
GROUP BY title, state
bookrecno 相关书目记录号
state 馆藏状态
barcode 馆藏标识
在 holding 分别是不是唯一的?
FROM(
SELECT title,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.bookrecno and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
)a
GROUP BY title这样显示的结果是对了
3,gm00007,gm00008
但我要的是gm00007-08这为一第记录显示,不连在一起的就和上面的写法一样,
有什么办法没,还有如果去掉where a.bookrecno=b.bookrecno and a.title='我的身体偷偷出轨'
这句当中的and a.title='我的身体偷偷出轨'
会出现"ORA-01489:字符串连接的结果过长".提示
bookrecno 相关书目记录号
state 馆藏状态
barcode 馆藏标识
在 holding 分别是不是唯一的?
肯定不能是唯一的
SELECT title,LEVEL ll, a.*, length(barcode) --, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.bookrecno and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title是什么?最大的level和length( barcode ) 是多少
有可能按照你的要求barcode连在一起,超过2000字符
SELECT title,LEVEL ll, a.*, length(barcode) --, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.bookrecno --and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
看看结果:
SELECT a.title, sum( length(b. barcode)), count(*)
from biblios a, holding b
where a.bookrecno=b.bookrecno
group by a.title
最大总长是多少
FROM(
SELECT title,state ,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title,b.state ORDER BY b.barcode) rr,
a.title , b.barcode, b.state
from biblios a, holding b
where a.bookrecno=b.bookrecno
-- and ....其他条件
-- and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title AND a.state = PRIOR a.state)a
GROUP BY title, state
在这里SELECT title,state 后面还有很多字段要显示,但我只要按title字段分组,我想去掉分组语句后面的state,就出现不是group by表达试
不然将面临全表关联,将是特别的慢
先可以用
SELECT a.title, sum( length(b. barcode)) + count(*) la
from biblios a, holding b
where a.bookrecno=b.bookrecno
group by a.title
把la 超过2000的 title 找到过滤掉SELECT title,substr( MAX(p)KEEP (DENSE_RANK LAST ORDER BY ll) ,2 ), MAX(ll)
FROM(
SELECT title,LEVEL ll, SYS_CONNECT_BY_PATH (barcode, ',') p
FROM (
select row_number()over(PARTITION BY a.title ORDER BY b.barcode) rr,
sum(length( b.barcode)+1)over(PARTITION BY a.title ORDER BY b.barcode) ls,
-- sum(length( b.barcode)+1)over(PARTITION BY a.title) la,
a.title , b.barcode
from biblios a, holding b
where a.bookrecno=b.bookrecno and a.title='我的身体偷偷出轨'
) a
START WITH a.rr=1
CONNECT BY ls<2000 and a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
)a
GROUP BY title*****************************
如果只要按title字段分组,
state 其它字段不是唯一的,怎么选取?否则得用:
SELECT title, max(state),....
FROM(
SELECT title,state,....
FROM (
select ....
a.title , b.barcode ,state,...
from biblios a, holding b
3,gm00007,gm00008
但我要的是gm00007-08这为一第记录显示,不连在一起的就和上面的写法一样
CONNECT BY ls<2000 这句话的意思是连接字符串少于2000的输出对吗,那还剩下的是不是ls>2000的输出?对吗
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 b.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 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< 2000 and a.rr = PRIOR a.rr +1
AND a.title = PRIOR a.title
)a
GROUP BY title这是个麻烦,还不如用自定义函数