vip会员统计表 （vip等级是灵活配置的 非写死1是金卡用户 2是什么 等）
一个非常常见的报表，分析会员组成比例 以及最新增长情况 和上月同期会员增长情况。
比较特殊一点的是 报表中的 普通会员 和 金卡会员 临时会员 银卡会员 等列 都是根据会员等级配置表动态生成的（即我是不知道会有多少个vip等级的）。
sql如下（有点挫的感觉 应该会有更优雅的方式实现）：
select N=1, AgentID, AgentBigArea, AgentName,CusALevel,CusCreateDate into #temp from Customer inner join CusAccount on
CusAVIPNo = VIPNo inner join Agent on AgentID = CusAgentID where CusStatus = 1 and CusCreateDate<'2016-03-31 23:59:59'
--插入区域小计
insert into #temp select SUM(N),-1,AgentBigArea+'_小计',AgentBigArea+'_小计',CusALevel,'' from #temp group by AgentBigArea,CusALevel
--插入总计
insert into #temp select SUM(N),-1,'总合计','总合计',CusALevel,'' from #temp where AgentBigArea not like '%小计%' group by CusALevel
declare @sql nvarchar(max)
CusAVIPNo = VIPNo inner join Agent on AgentID = CusAgentID where CusStatus = 1 and CusCreateDate<'2016-03-31 23:59:59'
--插入区域小计
insert into #temp select SUM(N),-1,AgentBigArea+'_小计',AgentBigArea+'_小计',CusALevel,'' from #temp group by AgentBigArea,CusALevel
--插入总计
insert into #temp select SUM(N),-1,'总合计','总合计',CusALevel,'' from #temp where AgentBigArea not like '%小计%' group by CusALevel
declare @sql nvarchar(max)
-- 空格这么多是因为不能设置字符串长度 很挫 暂时这样处理吧
set @sql = 'select '' '' as ''增长率'', Agentname as ''分销商'',agentid,AgentBigArea as ''区域'', SUM(case when CusCreateDate>''2016-03-01'' and CusCreateDate<''2016-03-31'' then N else 0 end) as ''本期'' ,
SUM(case when CusCreateDate>Dateadd(MONTH, -1, ''2016/3/1 0:00:00'') and CusCreateDate<Dateadd(MONTH, -1, ''2016/3/31 0:00:00'') then N else 0 end) as ''上期'',
SUM(N) as ''总会员数'''
--动态行转列
SELECT @sql = @sql + ',SUM(case CusALevel when ' +CAST(CusLevelID as nvarchar(10))+' then N else 0 end) as [' +CusLevelName+']'
FROM
(SELECT TOP 10000 CusLevelID,CusLevelName
FROM CusLevel
) AS a
print @sql
SET @sql = @sql + ' into ##temp1 from #temp group by agentname,agentid,AgentBigArea order by AgentBigArea '
EXEC (@sql)
set @sql = 'select '' '' as ''增长率'', Agentname as ''分销商'',agentid,AgentBigArea as ''区域'', SUM(case when CusCreateDate>''2016-03-01'' and CusCreateDate<''2016-03-31'' then N else 0 end) as ''本期'' ,
SUM(case when CusCreateDate>Dateadd(MONTH, -1, ''2016/3/1 0:00:00'') and CusCreateDate<Dateadd(MONTH, -1, ''2016/3/31 0:00:00'') then N else 0 end) as ''上期'',
SUM(N) as ''总会员数'''
--动态行转列
SELECT @sql = @sql + ',SUM(case CusALevel when ' +CAST(CusLevelID as nvarchar(10))+' then N else 0 end) as [' +CusLevelName+']'
FROM
(SELECT TOP 10000 CusLevelID,CusLevelName
FROM CusLevel
) AS a
print @sql
SET @sql = @sql + ' into ##temp1 from #temp group by agentname,agentid,AgentBigArea order by AgentBigArea '
EXEC (@sql)
--个人觉得比较挫的地方 用到了全局临时表
select * into #temp2 from ##temp1
drop table ##temp1
--计算区域小计行的本期和上期
update #temp2 set 本期=bq,上期=sq from #temp2 inner join (select 区域+'_小计' as AgentBigArea, SUM(上期) as sq,SUM(本期) as bq from #temp2
where 区域 not like '%小计%' group by 区域 ) as t on t.AgentBigArea = #temp2.区域 where #temp2.区域 like '%小计%'
--计算总合计的本期和上期
update #temp2 set 本期=(select SUM(本期) from #temp2 where 区域 like '%小计%' ),上期=(select SUM(上期) from #temp2 where 区域 like '%小计%' ) where 区域 = '总合计'
--计算增长率
update #temp2 set 增长率 = (case 上期 when 0 then '' else convert(varchar(30),cast((本期-上期)/convert(decimal(10,2),上期) *100 as decimal(18,2)) )+'%' end) from #temp2
select * from #temp2
select * into #temp2 from ##temp1
drop table ##temp1
--计算区域小计行的本期和上期
update #temp2 set 本期=bq,上期=sq from #temp2 inner join (select 区域+'_小计' as AgentBigArea, SUM(上期) as sq,SUM(本期) as bq from #temp2
where 区域 not like '%小计%' group by 区域 ) as t on t.AgentBigArea = #temp2.区域 where #temp2.区域 like '%小计%'
--计算总合计的本期和上期
update #temp2 set 本期=(select SUM(本期) from #temp2 where 区域 like '%小计%' ),上期=(select SUM(上期) from #temp2 where 区域 like '%小计%' ) where 区域 = '总合计'
--计算增长率
update #temp2 set 增长率 = (case 上期 when 0 then '' else convert(varchar(30),cast((本期-上期)/convert(decimal(10,2),上期) *100 as decimal(18,2)) )+'%' end) from #temp2
select * from #temp2
