字段 :
ID,dh(点号),cd(长度)
其内容为
i1,1,20
i1,2,30
i1,3,10i2,1,10
i2,2,15
i2,3,5怎么根据这个表生成一个新的表,字段为id,dh1,dh2,cd
其中的内容是这样的,对id号相同的数据进行处理,生成
i1,1,2,50(相当于点号1长度)+点号2长度))
i1,2,3, 40(相当于点2长度)+点3长度))
i1,3,1 30 (相当于点3长度)+点1长度))i2 ,1,2 ,10 (点1长度+点2长度)
i2, 2,3, 15 (同上)
i2, 3,1 , 5 这个语句怎么写呢
ID,dh(点号),cd(长度)
其内容为
i1,1,20
i1,2,30
i1,3,10i2,1,10
i2,2,15
i2,3,5怎么根据这个表生成一个新的表,字段为id,dh1,dh2,cd
其中的内容是这样的,对id号相同的数据进行处理,生成
i1,1,2,50(相当于点号1长度)+点号2长度))
i1,2,3, 40(相当于点2长度)+点3长度))
i1,3,1 30 (相当于点3长度)+点1长度))i2 ,1,2 ,10 (点1长度+点2长度)
i2, 2,3, 15 (同上)
i2, 3,1 , 5 这个语句怎么写呢
select t1.id,t1.dh||','||t2.dh||','||(t1.cd+nvl(t2.cd,0))cd
from tt t1,tt t2
where t1.id=t2.id(+)-1
with tt as(select 'i1' id,1 dh,20 cd from dual
union all select 'i1',2,30 from dual
union all select 'i1',3,10 from dual
union all select 'i2',1,10 from dual
union all select 'i2',2,15 from dual
union all select 'i2',3,5 from dual)
select id,dh,nvl(lead(dh)over(partition by id order by dh),min(dh)over(partition by id)) dh,
cd+nvl(lead(cd)over(partition by id order by dh),max(cd)keep(dense_rank first order by dh)over(partition by id)) cd
from ttID DH DH CD
i1 1 2 50
i1 2 3 40
i1 3 1 30
i2 1 2 25
i2 2 3 20
i2 3 1 15
是这样吧
刚才还在那想怎么写的. 用自连接真的很简单呀.生成新表直接用上面查询结果就可以了。create table Table_name as select t1.id,t1.dh||','||t2.dh||','||(t1.cd+nvl(t2.cd,0))cd from tt t1,tt t2 where t1.id=t2.id(+)-1
from tt研究下 这些函数. 写SQL的能力太差了..
select id,dh1,dh2,cd from
(select id,dh dh1,lead(dh) over(partition by id order by rownum) dh2,cd + lead(cd) over(partition by id order by rownum) cd from
(select id,dh,cd from tb union all select x.id,x.dh,x.cd from tb x,(select id,min(dh) dh from tb group by id) y where x.id = y.id and x.dh = y.dh))
where dh2 is not null;
还是#3的比较好。
---- ---------- ----------
i1 1 20
i1 2 30
i1 3 10
i2 1 10
i2 2 15
i2 3 5已选择6行。已用时间: 00: 00: 00.00
11:29:52 scott@TUNGKONG> select id,dh1,dh2,cd from
11:30:02 2 (select id,dh dh1,lead(dh) over(partition by id order by rownum) dh2,cd + lead(cd) over(partition by id order by rownum) cd from
11:30:02 3 (select id,dh,cd from tb union all select x.id,x.dh,x.cd from tb x,(select id,min(dh) dh from tb group by id) y where x.id = y.id and x.dh = y.dh))
11:30:02 4 where dh2 is not null;ID DH1 DH2 CD
---- ---------- ---------- ----------
i1 1 2 50
i1 2 3 40
i1 3 1 30
i2 1 2 25
i2 2 3 20
i2 3 1 15已选择6行。已用时间: 00: 00: 00.01
11:30:44 scott@TUNGKONG> select id,dh dh1,nvl(lead(dh)over(partition by id order by dh),min(dh)over(partition by id)) dh2,
11:30:59 2 cd+nvl(lead(cd)over(partition by id order by dh),max(cd)keep(dense_rank first order by dh)over(partition by id)) cd
11:30:59 3 from tb;ID DH1 DH2 CD
---- ---------- ---------- ----------
i1 1 2 50
i1 2 3 40
i1 3 1 30
i2 1 2 25
i2 2 3 20
i2 3 1 15已选择6行。已用时间: 00: 00: 00.03
cd+nvl(lead(cd)over(partition by id order by dh),first_value(cd)over(partition by id order by dh)) cd
from tt