如何把两条记录合并生成一条

[jieguo]

字段 ：
ID，dh（点号），cd（长度）
其内容为
i1,1,20
i1,2,30
i1,3,10

i2,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

这个语句怎么写呢

[jieguo]

上面的点号都是字符串 类型的

[小灰狼W]

连接一下就好了
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

[小灰狼W]

。。看错

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 tt

ID	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

是这样吧

[Dave]

[Quote=引用 2 楼 wildwave 的回复:]
连接一下就好了
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

[/Quote]

刚才还在那想怎么写的. 用自连接真的很简单呀.

生成新表直接用上面查询结果就可以了。

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

[Dave]

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 tt

研究下 这些函数. 写SQL的能力太差了..

[jieguo]

不好意思了，好像达不到我要的效果，我的意思是 dh是字符串类型的，cd（长度）是数值型的

我要吧一个 三角形，的一个点和下一个点连成一条线，最后，得到一个新的表，里面的长度是一条记录的长度+下一条记录的长度，最后一条的长度要加上第一条的长度，

[小灰狼W]

那么，什么地方不能满足你的要求？
dh是字符型还是数值型不影响结果

[liusong_china]

只想到个笨方法：

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的比较好。。。。

[liusong_china]

11:29:49 scott@TUNGKONG> select * from tb;

ID   DH                 CD
---- ---------- ----------
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

[小灰狼W]

也可以这样

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),first_value(cd)over(partition by id order by dh)) cd
from tt