declare @t table(bm varchar(10),sb varchar(10),price float)
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
declare @t table(bm varchar(10),sb varchar(100),price float)
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 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
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)+'%'