F(项目) A(基数) B(倍数) C(结果)
01 100 5 500
01 222 3 666
01 508 9 4572
02 800 0.5 400
02 140 0.1 14
02 33333 0.8 26666.4
03 151 220 33220
03 250 440 110000
03 808 154 124432查询结果: F(项目) A(基数) B(倍数) C(结果)
01 830 6.913 5738
02 33973 0.797 27080.4
03 1209 221.383 4572
中间那个倍数是总结果除以基数的值等到强援之手~
01 100 5 500
01 222 3 666
01 508 9 4572
02 800 0.5 400
02 140 0.1 14
02 33333 0.8 26666.4
03 151 220 33220
03 250 440 110000
03 808 154 124432查询结果: F(项目) A(基数) B(倍数) C(结果)
01 830 6.913 5738
02 33973 0.797 27080.4
03 1209 221.383 4572
中间那个倍数是总结果除以基数的值等到强援之手~
FROM TB
GROUP BY F
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([F] varchar(2),[A] int,[B] numeric(4,1),[C] numeric(7,1))
insert [TB]
select '01',100,5,500 union all
select '01',222,3,666 union all
select '01',508,9,4572 union all
select '02',800,0.5,400 union all
select '02',140,0.1,14 union all
select '02',33333,0.8,26666.4 union all
select '03',151,220,33220 union all
select '03',250,440,110000 union all
select '03',808,154,124432select F,SUM(A) AS A,CAST(SUM(C)/SUM(A) AS DEC(18,3)) AS B,SUM(C) AS C
FROM TB
GROUP BY F--测试结果:
/*
F A B C
---- ----------- --------------------------------------- ---------------------------------------
01 830 6.913 5738.0
02 34273 0.790 27080.4
03 1209 221.383 267652.0(3 行受影响)
*/
insert into @t select '01',100 ,5 ,500
insert into @t select '01',222 ,3 ,666
insert into @t select '01',508 ,9 ,4572
insert into @t select '02',800 ,0.5,400
insert into @t select '02',140 ,0.1,14
insert into @t select '02',33333,0.8,26666.4
insert into @t select '03',151 ,220,33220
insert into @t select '03',250 ,440,110000
insert into @t select '03',808 ,154,124432 select
F,sum(A) A,cast(sum(C)/sum(A) as numeric(10,3)) B,sum(C) C
from
@t
group by
F/*
F A B C
---------- ------------- ------------ ------------
01 830.00 6.913 5738.00
02 34273.00 .790 27080.40
03 1209.00 221.383 267652.00
*/
select [F(项目)], [A(基数)]=sum([A(基数)]), [B(倍数)]= cast(sum([C(结果)])*1.0/sum([A(基数)]) as decimal(10,1))
from tb
group by [F(项目)]