一个非常常见的报表，分析会员组成比例 以及最新增长情况 和上月同期会员增长情况。比较特殊一点的是 报表中的 普通会员 和 金卡会员 临时会员 银卡会员 等列 都是根据会员等级配置表动态生成的（即我是不知道会有多少个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) — 空格这么多是因为不能设置字符串长度 很挫 暂时这样处理吧
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
