select a.id,
sum1,Rate1=sum1/b.TOTAL,
sum2,Rate2=sum2/b.TOTAL,
sum3,Rate3=sum3/b.TOTAL,
TOTAL
from tb a,(
select id=(id-1)/2,TOTAL=sum(TOTAL)
from tb
group by (id-1)/2
)b where a.id between b.id*2+1 and b.id*2+2
sum1,Rate1=sum1/b.TOTAL,
sum2,Rate2=sum2/b.TOTAL,
sum3,Rate3=sum3/b.TOTAL,
TOTAL
from tb a,(
select id=(id-1)/2,TOTAL=sum(TOTAL)
from tb
group by (id-1)/2
)b where a.id between b.id*2+1 and b.id*2+2
sum1,Rate1=cast(sum1/b.TOTAL as decimal(5,2)),
sum2,Rate2=cast(sum2/b.TOTAL as decimal(5,2)),
sum3,Rate3=cast(sum3/b.TOTAL as decimal(5,2)),
a.TOTAL
from tb a,(
select id1=min(id),id2=max(id),TOTAL=sum(TOTAL)*1.
from tb
group by (id-1)/2
)b where a.id between b.id1 and b.id2
insert tb select 1,2,3,4,9
union all select 2,2,4,1,7
union all select 3,1,3,3,7
union all select 4,3,4,5,12
go--查询
select a.id,
sum1,Rate1=cast(sum1/b.TOTAL as decimal(5,2)),
sum2,Rate2=cast(sum2/b.TOTAL as decimal(5,2)),
sum3,Rate3=cast(sum3/b.TOTAL as decimal(5,2)),
a.TOTAL
from tb a,(
select id1=min(id),id2=max(id),TOTAL=sum(TOTAL)*1.
from tb
group by (id-1)/2
)b where a.id between b.id1 and b.id2
go--删除测试
drop table tb/*--结果
id sum1 Rate1 sum2 Rate2 sum3 Rate3 TOTAL
----- ----- ------- ----- ------ ------ ------- -------
1 2 .13 3 .19 4 .25 9
2 2 .13 4 .25 1 .06 7
3 1 .05 3 .16 3 .16 7
4 3 .16 4 .21 5 .26 12(所影响的行数为 4 行)
--*/