select sb,
convert(varchar(10),(select price/sum(price)*100 from 表 where bm=a.bm group by bm))+'%',
convert(varchar(10),(select sum(price) from 表 where sb=a.sb group by sb)/(select sum(price) from 表)*100)+'%'from 表 a
convert(varchar(10),(select price/sum(price)*100 from 表 where bm=a.bm group by bm))+'%',
convert(varchar(10),(select sum(price) from 表 where sb=a.sb group by sb)/(select sum(price) from 表)*100)+'%'from 表 a
insert @t
select 'a部','电脑',50000 union all
select 'b部','电脑',30000 union all
select 'c部','桌椅',5000 union all
select 'a部','空调',8000 union all
select 'd部','空调',6000select bm=t.bm,
sb=t.sb,
perinbm=cast((t.price/b.bmttl)*100 as varchar) +'%',
perinall=cast((t.price/b.allttl)*100 as varchar)+'%'
from @t t, (
select bm=bm,
sb=sb,
bmttl=(select sum(price) from @t where bm=a.bm group by bm) ,
allttl=(select sum(price) from @t)
from @t a
) b
where t.bm=b.bm and t.sb=b.sb
order by t.bm,t.sb
(所影响的行数为 5 行)bm sb perinbm perinall
---------- ---------- ------------------------------- -------------------------------
a部 电脑 86.2069% 50.5051%
a部 空调 13.7931% 8.08081%
b部 电脑 100% 30.303%
c部 桌椅 100% 5.05051%
d部 空调 100% 6.06061%(所影响的行数为 5 行)
insert @t
select 'a部','电脑',50000 union all
select 'b部','电脑',30000 union all
select 'c部','桌椅',5000 union all
select 'a部','空调',8000 union all
select 'd部','空调',6000
select t.bm,
t.sb,
t.price,
per_1=cast(((t.price)/b.bm_zl)*100 as varchar(20))+'%',
per_2=cast((b.sb_zl/b.zl)*100 as varchar(20))+'%'
from @t t,
(select
bm,
sb,
bm_zl=(select sum(price) from @t where bm=a.bm),
sb_zl=(select sum(price) from @t where sb=a.sb group by sb),
zl=(select sum(price) from @t)
from @t a)b
where t.bm=b.bm and t.sb=b.sb
order by t.bm,t.sb----结果
bm sb price per_1 pre_1
a部 电脑 50000.0 86.2069% 80.8081%
a部 空调 8000.0 13.7931% 14.1414%
b部 电脑 30000.0 100% 80.8081%
c部 桌椅 5000.0 100% 5.05051%
d部 空调 6000.0 100% 14.1414%