try it ... SQL> select * from notest; NID A B C ---------- ------ ------ ------ 1 X X Z 1 M N 1 L ESQL:select distinct zz.nid, first_value(replace(rtrim(replace(ltrim(zz.a||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as a, first_value(replace(rtrim(replace(ltrim(zz.b||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as b, first_value(replace(rtrim(replace(ltrim(zz.c||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as c from ( select tt.nid, sys_connect_by_path(tt.a,',') as a, sys_connect_by_path(tt.b,',') as b, sys_connect_by_path(tt.c,',') as c, level lev from ( select ot.nid, decode(ot.a,null,'|',ot.a) as a, decode(ot.b,null,'|',ot.b) as b, decode(ot.c,null,'|',ot.c) as c, ot.nid||(row_number() over(partition by ot.nid order by ot.nid)-1) as front, ot.nid||row_number() over(partition by ot.nid order by ot.nid) as behind from notest ot )tt connect by prior tt.front = tt.behind )zz; RESULT: NID A B C ---------- --------- ---------- --------- 1 L|X M|X E|N|Z
create table nos(no int,A varchar(10),B varchar(10),C varchar(10)) insert into nos select 1,'x','x','z' union all select 1,null,'m','n' union all select 1,'l',null,'e'create proc void as declare @t table(no int,A varchar(10),B varchar(10),C varchar(10)) declare @a varchar(500) declare @b varchar(500) declare @c varchar(500) select @a=isnull(@a,'')+isnull(a+'|',''), @b=isnull(@b,'')+ isnull(b +'|',''), @c=isnull(@c,'')+ c +'|' from nos where no=1 insert into @t select 1,left(@a,len(@a)-1),left(@b,len(@b)-1),left(@c,len(@c)-1) select * from @t--调用存储过程: exec void
with a as (select 1 no,'x' a,'x' b,'z' c from dual union select 1 no,NULL a,'m' b,'n' c from dual union select 1 no,'l' a,null b,'e' c from dual ) select no,max(sys_connect_by_path(a,' ')) rm,max(sys_connect_by_path(b,' ')) rm1,max(sys_connect_by_path(c,' ')) rm2 from (select no,a,b,c,row_number()over(partition by no order by no) rn from a) b start with rn=1 connect by rn-1=prior rn group by no
結果是這樣的: 1 l x m x e n z
来个简单点的 select x.a, max(trim('|' from sys_connect_by_path(b, '|'))), max(trim('|' from sys_connect_by_path(c, '|'))), max(trim('|' from sys_connect_by_path(d, '|'))) from (select t.*, row_number() over(partition by t.a order by t.a) n from (select 1 a, 'x' b, 'x' c, 'z' d from dual union select 1 a, null b, 'm' c, 'n' d from dual union select 1 a, 'l' b, null c, 'e' d from dual) t ) x connect by n = prior n + 1 start with x.n = 1 group by x.a
比如 你的C属性,定义一个char数据,第一次读到Z你输入Z之后在第二次读取记录且写入时间差是否已经有相同的,不然继续写入,写到第二位,一次类推不就可以了吗?
SQL> select * from notest; NID A B C
---------- ------ ------ ------
1 X X Z
1 M N
1 L ESQL:select distinct
zz.nid,
first_value(replace(rtrim(replace(ltrim(zz.a||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as a,
first_value(replace(rtrim(replace(ltrim(zz.b||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as b,
first_value(replace(rtrim(replace(ltrim(zz.c||',',','),'|,',''),','),',','|')) over(partition by zz.nid order by lev desc) as c
from (
select tt.nid,
sys_connect_by_path(tt.a,',') as a,
sys_connect_by_path(tt.b,',') as b,
sys_connect_by_path(tt.c,',') as c,
level lev
from (
select ot.nid,
decode(ot.a,null,'|',ot.a) as a,
decode(ot.b,null,'|',ot.b) as b,
decode(ot.c,null,'|',ot.c) as c,
ot.nid||(row_number() over(partition by ot.nid order by ot.nid)-1) as front,
ot.nid||row_number() over(partition by ot.nid order by ot.nid) as behind
from notest ot
)tt
connect by prior tt.front = tt.behind
)zz;
RESULT: NID A B C
---------- --------- ---------- ---------
1 L|X M|X E|N|Z
create table nos(no int,A varchar(10),B varchar(10),C varchar(10))
insert into nos select 1,'x','x','z'
union all select 1,null,'m','n'
union all select 1,'l',null,'e'create proc void
as
declare @t table(no int,A varchar(10),B varchar(10),C varchar(10))
declare @a varchar(500)
declare @b varchar(500)
declare @c varchar(500)
select @a=isnull(@a,'')+isnull(a+'|',''), @b=isnull(@b,'')+ isnull(b +'|',''), @c=isnull(@c,'')+ c +'|' from nos where no=1
insert into @t select 1,left(@a,len(@a)-1),left(@b,len(@b)-1),left(@c,len(@c)-1)
select * from @t--调用存储过程:
exec void
union
select 1 no,NULL a,'m' b,'n' c from dual
union
select 1 no,'l' a,null b,'e' c from dual
)
select no,max(sys_connect_by_path(a,' ')) rm,max(sys_connect_by_path(b,' ')) rm1,max(sys_connect_by_path(c,' ')) rm2 from
(select no,a,b,c,row_number()over(partition by no order by no) rn from a) b
start with rn=1
connect by rn-1=prior rn
group by no
結果是這樣的:
1 l x m x e n z
select x.a,
max(trim('|' from sys_connect_by_path(b, '|'))),
max(trim('|' from sys_connect_by_path(c, '|'))),
max(trim('|' from sys_connect_by_path(d, '|')))
from
(select t.*, row_number() over(partition by t.a order by t.a) n
from (select 1 a, 'x' b, 'x' c, 'z' d from dual
union
select 1 a, null b, 'm' c, 'n' d from dual
union
select 1 a, 'l' b, null c, 'e' d from dual) t
) x
connect by n = prior n + 1
start with x.n = 1
group by x.a