select
门店号,
消费会员数 = count(distinct 会员号) - max(case when 会员号 is null then 1 else 0 end),
会员销售额 = sum(case when 会员号 is not null then 销售额 else 0 end),
总销售额 = isnull(sum(销售额),0),
占比 = (sum(case when 会员号 is not null then 销售额 else 0 end)*1.0)/(case when isnull(sum(销售额),0)=0 then 1 else sum(销售额) end)
from
表
where
销售时间 Between '20005-4-1' and '2005-4-10' --时间范围条件
group by
门店号
select
门店号,
消费会员数 = count(distinct 会员号) - max(case when 会员号 is null then 1 else 0 end),
会员销售额 = sum(case when 会员号 is not null then 销售额 else 0 end),
总销售额 = isnull(sum(销售额),0),
占比 = (sum(case when 会员号 is not null then 销售额 else 0 end)*1.0)/(case when isnull(sum(销售额),0)=0 then 1 else sum(销售额) end)
from
表
where
... --时间范围条件
group by
门店号
select
门店号,
消费会员数=count(distinct(会员号)) --這裡可能出錯
会员销售额=sum(case when 会员号 is not null then 销售额 else 0 end),
总销售额=sum(销售额),
占比= cast(cast( 会员销售额/总销售额*100 as decimal(16,2)) as varchar(10)))+'%' --2位小數
from 表
where 在一段时间内
group by 门店号
select
门店号,
消费会员数 = count(distinct 会员号) - max(case when 会员号 is null then 1 else 0 end),
会员销售额 = sum(case when 会员号 is not null then 销售额 else 0 end),
总销售额 = isnull(sum(销售额),0),
占比 = (sum(case when 会员号 is not null then 销售额 else 0 end)+0.0)/(case when isnull(sum(销售额),0)=0 then 1 else sum(销售额) end)
from
表
where
... --时间范围条件
group by
门店号