sql表插入行的问题？

[ouyang66]

我关联两张表查询出如下结果：
列1 列2 列3
1 1.2 1.2
2 1.3 1.3
3 1.4 1.4
.
.
12 2.0 2.0
1 3.3 3.4
2 . .
第一列是从1到12然后再从1到12第二和三列是数字
现在要的效果是，在每行后都插入一行，而且第一列与上一行一样，第二、三列分别是上面行的和即：
1 1.2 1.2
1 1.2 1.2
2 1.3 1.3
2 2.5 2.5
3 1.4 1.4
3 3.9 3.9

请各位高手帮帮忙，谢谢了

[ouyang66]

要写一个查询就好了，因为我要用到的是一个视图。存储过程也行啦，

[pt1314917]

declare @t table(a int,b numeric(5,2),c numeric(5,2))
insert into @t select 1,1.2,1.2
insert into @t select 2,1.3,1.3
insert into @t select 3,1.4,1.4
insert into @t select 4,1.5,1.5
insert into @t select 5,1.6,1.6

select * from
(select * from @t
union all
select a,(select sum(b) from @t where a<=t.a),(select sum(c) from @t where a<=t.a) from @t t)k
order by a,b

[pt1314917]

如果子查询效率太低，可以换成：
declare @t table(a int,b numeric(5,2),c numeric(5,2))
insert into @t select 1,1.2,1.2
insert into @t select 2,1.3,1.3
insert into @t select 3,1.4,1.4
insert into @t select 4,1.5,1.5
insert into @t select 5,1.6,1.6

select * from
(select * from @t
union all
select t.a,sum(h.b),sum(h.c) from @t t,@t h where h.a<=t.a group by t.a)k
order by a,b

[ojuju10]

declare @t table(a int,b numeric(5,2),c numeric(5,2))
insert into @t select 1,1.2,1.2
insert into @t select 2,1.3,1.3
insert into @t select 3,1.4,1.4
insert into @t select 4,1.5,1.5
insert into @t select 5,1.6,1.6


select * from @t a
union all
select * from (
select a.a,sum(b.b) b,sum(b.c) c from @t a,@t b
where a.a>=b.a
group by a.a ) b 
order by a.a,a.b

a           b                                       c
----------- --------------------------------------- ---------------------------------------
1           1.20                                    1.20
1           1.20                                    1.20
2           1.30                                    1.30
2           2.50                                    2.50
3           1.40                                    1.40
3           3.90                                    3.90
4           1.50                                    1.50
4           5.40                                    5.40
5           1.60                                    1.60
5           7.00                                    7.00

(10 行受影响)