比如 no A B C
1 X X Z
1 M N
1 L E==>
no A B C
1 X|L X|M Z|N|E谢谢~~~
1 X X Z
1 M N
1 L E==>
no A B C
1 X|L X|M Z|N|E谢谢~~~
解决方案 »
- dblink建立的步骤有哪些?
- key构成与记录个数的关系!
- 基于还原点的还原是不是不需要归档模式啊?
- 查看job上次执行了多长时间
- 简单的SQL,请高手帮忙,希望用一个SQL查询实现
- 关于JDBC连接ORACLE11的问题
- 一个看上简单,实际很复杂的问题--树型数据结构的库表设计问题?
- 同时有几个客户端的plsql同时登录到数据库中修改数据,各个客户端各自点击回滚都能恢复到原始状态吗?还是只能恢复到最后一个plsql的操作
- 100分求解!为该监听程序提供的信息正由此计算机上的其他监听程序使用,可以按现状继续配置,但只有在解决冲突之后才能启动该监听程序。
- 用触发器中,我想得到当前被UpDate的行的rowid?应该如何实现???
- 求一 SQL 语句
- ldap 扩展功能——分页查询设置问题
比如 你的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