select 区域,
cde毛利 = sum(cde1),
cde毛利率 = cast(round(sum(cde1)*100.0/cdesl,2) as varchar)+'%',
erer毛利 = sum(erer1),
erer毛利率 = cast(round(sum(erer1)*100.0/erersl,2) as varchar)+'%'
from (select 区域,
cde1 = sum(case when charindex('cde',产品名) > 0 then 销售额- 成本 else 0 end) as cde1,
sum(case when charindex('cde',产品名) > 0 then 销量 else 0 end) as cdesl ,
erer1 = sum(case when charindex('erer',产品名) > 0 then 销售额- 成本 else 0 end) as erer1,
sum(case when charindex('erer',产品名) > 0 then 销量 else 0 end) as erersl
from ta group by 区域) a
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (区域 varchar(8),产品名 varchar(8),销量 int,销售额 money,成本 money)
insert into #T
select 'A省','abcde',200,4000,5000 union all
select 'B省','cdefg',10,200,300 union all
select 'A省','werer',100,3000,4000 union all
select 'C省','erery',80,1000,1800select 区域,cde毛利=cde1-cde2, de毛利率=ltrim(isnull((cde1-cde2)/nullif(cde2,0)*100,'0.00'))+'%',erer毛利=erer1-erer2, de毛利率=ltrim(isnull((erer1-erer2)/nullif(erer2,0)*100,'0.00'))+'%' from
(
select
区域=isnull(区域,'合计'),
cde1=sum(case when 产品名 like '%cde%' then 成本 else 0 end),
cde2=sum(case when 产品名 like '%cde%' then 销售额 else 0 end),
erer1=sum(case when 产品名 like '%erer%' then 成本 else 0 end),
erer2=sum(case when 产品名 like '%erer%' then 销售额 else 0 end)
from #T
group by 区域 with rollup
) t/*
区域 cde毛利 de毛利率 erer毛利 de毛利率
--------- --------- --------- --------- ---------
A省 1000.00 25.00% 1000.00 33.33%
B省 100.00 50.00% 0.00 0.00%
C省 0.00 0.00% 800.00 80.00%
合计 1100.00 26.19% 1800.00 45.00%
*/