从表A转换到表B

[weihouyanli]

从表A转换到表B;用SQL实现

Table A:
　 　
　 col1 　 　
a 　
b 　
c 　
d 　
e



Table B: 　 　
col1 col2
a (a+b)/2
a+b (a+b+c)/3
a+b+c (b+c+d)/3
a+b+c+d (c+d+e)/3
a+b+c+d+e (d+e)/2

[Generics]

就5行数据?

SET NOCOUNT ON
create TAble A (col1 float, col2 float)

insert A (col1)
select 1
union all select 2
union all select 3
union all select 4
union all select 5
GO

With Ac (rowID, col1, col2)
AS
(	Select row_number() over(order by Current_TimeStamp) as rowID, col1, col2
	From A
)

select (select sum(col1) from Ac where rowID <= Ac1.rowID) as col1,  
	CASE WHEN rowid= 1 THEN (select sum(col1)/2 from AC where rowID <=2)
		WHEN rowID = (select max(rowID) from Ac) THEN (select sum(col1)/2 from AC where
		rowID >= Ac1.rowID-1)
		ELSE (select sum(col1/3) from AC where rowID >= Ac1.rowID-1 AND rowID <= Ac1.rowID+1)
	END as col2
FROM Ac Ac1

drop table A

>>>>

col1 col2
---------------------- ----------------------
1 1.5
3 2
6 3
10 4
15 4.5

[JasonJHu]

不是很理解楼主的意思

[wgzaaa]

是计算值还是显示字符？是计算：
create table A(col1 int)
insert A select 3
insert A select 2
insert A select 5
insert A select 1
insert A select 4
-----------------------------------------------------------
select *,identity(int,1,1) Cid into #B from A--借用临时表
select col1,(select sum(col1) from #B b where b.cid<=a.cid)累计和,
(select avg(col1*1.0) from #B c where abs(c.cid-a.cid)<=1)前后平均数 from #B a
------------------------------------
col1 累计和 前后平均数
----------- ----------- ----------------------------------------
3 3 2.500000
2 5 3.333333
5 10 2.666666
1 11 3.333333
4 15 2.500000

（所影响的行数为 5 行）

[playwarcraft]

col2怎么算的？

[weihouyanli]

col2
(a+b)/2
(a+b+c)/3
(b+c+d)/3
(c+d+e)/3
(d+e)/2


就是显示成这样,没有规律性