--清楼主试试:
select 分店编号,经销类型,sum(实收总额),sum(实收总额)/(case (select isnull(sum(实收总额),0) from UV_SALE where 分店编号=a.分店编号) when 0 then 1 else (select isnull(sum(实收总额),0) from UV_SALE where 分店编号=a.分店编号) end) as 实收分店占比,sum(实收总额)/(case (select isnull(sum(实收总额),0) from UV_SALE) when 0 then 1 else (select isnull(sum(实收总额),0) from UV_SALE) end) as 实收总占比
from UV_SALE a
group by 分店编号,经销类型
select 分店编号,经销类型,sum(实收总额),sum(实收总额)/(case (select isnull(sum(实收总额),0) from UV_SALE where 分店编号=a.分店编号) when 0 then 1 else (select isnull(sum(实收总额),0) from UV_SALE where 分店编号=a.分店编号) end) as 实收分店占比,sum(实收总额)/(case (select isnull(sum(实收总额),0) from UV_SALE) when 0 then 1 else (select isnull(sum(实收总额),0) from UV_SALE) end) as 实收总占比
from UV_SALE a
group by 分店编号,经销类型
(
[分店编号] varchar(10),
[经销类型] varchar(10),
[实收额] decimal(10,2),
[毛利额] decimal(10,2),
[销售日期] varchar(10)
)
insert @tb
select '0000','01',20.00,2.00,'2005-08-20' union all
select '0000','01',20.00,2.00,'2005-08-20' union all
select '0000','01',30.00,3.00,'2005-08-20' union all
select '0000','02',30.00,3.00,'2005-08-20' union all
select '0001','01',20.00,3.00,'2005-08-20' union all
select '0001','02',80.00,7.00,'2005-08-20' --测试
select A.[分店编号],
A.[经销类型],
[实收总额]=sum([实收额]),
[实收分店占比]=(sum([实收额])+0.0)/B.[本店总额],
[实收总占比]=(sum([实收额])+0.0)/C.[所有店金额]
from @tb A
join (
select [分店编号],
[本店总额]=sum([实收额])
from @tb group by [分店编号]
)B on A.[分店编号]=B.[分店编号]
join (
select [所有店金额]=sum([实收额])
from @tb
)C on 1>0
group by A.[分店编号],
A.[经销类型],
B.[本店总额],
C.[所有店金额]--结果
/*
分店编号 经销类型 实收总额 实收分店占比 实收总占比
---------- ---------- -------------------------------- ----------
0000 01 70.00 .70 .35
0000 02 30.00 .30 .15
0001 01 20.00 .20 .10
0001 02 80.00 .80 .40(所影响的行数为 4 行)
*/
declare @tb table
(
[分店编号] varchar(10),
[经销类型] varchar(10),
[实收额] decimal(10,2),
[毛利额] decimal(10,2),
[销售日期] varchar(10)
)
insert @tb
select '0000','01',20.00,2.00,'2005-08-20' union all
select '0000','01',20.00,2.00,'2005-08-20' union all
select '0000','01',30.00,3.00,'2005-08-20' union all
select '0000','02',30.00,3.00,'2005-08-20' union all
select '0001','01',20.00,3.00,'2005-08-20' union all
select '0001','02',80.00,7.00,'2005-08-20' select 分店编号,经销类型,sum(实收额) as 实收总额 ,sum(实收额)/(case (select isnull(sum(实收额),0) from @tb where 分店编号=a.分店编号) when 0 then 1 else (select isnull(sum(实收额),0) from @tb where 分店编号=a.分店编号) end) as 实收分店占比,sum(实收额)/(case (select isnull(sum(实收额),0) from @tb) when 0 then 1 else (select isnull(sum(实收额),0) from @tb) end) as 实收总占比
from @tb a
group by 分店编号,经销类型/*
结果
分店编号 经销类型 实收总额 实收分店占比 实收总占比
------------------------------------------------------------------
0000 01 70.00 .700000 .350000
0000 02 30.00 .300000 .150000
0001 01 20.00 .200000 .100000
0001 02 80.00 .800000 .400000*/