但是我用了
select sid,isfill,substr(max(sys_connect_by_path(ocontent,';')),2) arr
from (
select sid,isfill,ocontent,row_number() over(order by ocontent) rn
from qc_subject_option where sid=40012
)
start with rn=1
connect by rn-1=prior rn
group by sid,isfill;
如果只考虑实现,不考虑性能.可以考虑使用分析函数:lead(x) over(partition by xx order by xxx)
create table t (
ID NUMBER(2)
TITLE VARCHAR2(20)
TYPE VARCHAR2(8)
AWNER VARCHAR2(10)
PERSON VARCHAR2(10)
);
insert into t values(1,'最爱球队','多选','马刺,'小明');
insert into t values(1,'最爱球队','多选','湖人','小明');
insert into t values(1,'最爱球队','多选','湖x人','小明');
insert into t values(2,'最爱教练,'多选','姐夫','小明');
insert into t values(2,'最爱教练,'多选','禅师','小明');
insert into t values(3,'最爱裁判,'多选','光头','小明');
select id,title,type,rtrim(awner,',') awner,person
from(
select id,title,type,rn,
awner||','||lead(awner) over(partition by id order by id) awner,
person
from (
select id,title,type,
awner||','||lead(awner) over(partition by id order by id) awner,
row_number() over(partition by id order by id) rn ,
person
from t
)
where mod(rn,2)<>0
)
where rn=1
我的是oracle9i
select sid,ocontent from qc_subject_option where sid=40012;
得出以下结果
1 40012 <div>A.1部</div>
2 40012 <div>B.2部</div>
3 40012 <div>C.3部</div>
4 40012 <div>D.3部以上(请填写)</div>
按照你给的连接,我看了,写了
select sid,substr(max(sys_connect_by_path(ocontent,'/')),2) arr
from (
select sid,ocontent,row_number() over(order by ocontent) rn
from qc_subject_option where sid=40012
)
start with rn=1
connect by rn-1=prior rn
group by sid;
但是报错
ora-30004:when using sys_connect_by_path function,cannot have seperator as part of column value