在sqlserver下巧用行列转换日期的数据统计 前言 在sqlserver中有很多统计函数的基础语法，有使用group by或partition by后配合sum,count(*)等用法。常应用于统计网站的pv流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互
                 在sqlserver下巧用行列转换日期的数据统计 
前言    在sqlserver 中有很多统计函数的基础语法，有使用group by 或 partition by 后配合sum,count(*) 等用法。常应用于统计网站的pv流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互相学习下：） 
背景        合同中行项目按月收入的统计
  1.业务逻辑及需求   1.1 表业务逻辑     合同是公司间互相签署的法律契约，一份合同从诞生起，就开始流转于公司的各个部门，最核心的还是盈亏的数值。盈亏是结果，数据的产生源于每个自然月或其他时段的汇总。 往往在实际业务中，例如有些广告行业，立项是分为固定排期和合同活动收入。  
   固定排期一般以一个自然月为周期，例如[201503,201504]间产生的预收入；活动收入表中的活动是指收入周期不固定，可能confirmdate  发生在一个月中的若干天中，也可能在间隔一个月后发生。
   无论是固定排期还是活动收入都和行项目有关，行项目是一个编号，一个行项目可以对应多次排期或活动收入的统计,在我给大家介绍的demo中，将暂时考虑固定排期的情况。
 1.2 项目的需求   统计合同中行项目的金额：分为结转金额数据汇总，和按自然月条件下金额的汇总。
2.准备的基础表 
    2.1 合同信息表 
create table contractinfo --基本信息表([contractcode] [varchar](50) primary key,[customname] [varchar](100) null,)insert into contractinfo(contractcode,customname)values('30100013000861','弘化四方') ,('30100013000862','明心见性') ,('30100013000863','心绽莲花')
2.2 合同行项目表
create table contractline --合同行项目表( [lineid] [int] identity(1,1) primary key not null, [contractcode] [varchar](50) not null, )insert into contractline(contractcode)values('30100013000861') ,('30100013000862') ,('30100013000862') ,('30100013000863') ,('30100013000863')
2.3 合同固定排期表
create table contractschedule --合同固定排期表( [scheduleid] [int] primary key not null,-- 排期id [lineid] [int] not null, -- 行项目id [period] [int] not null, --时间段 [amount] [decimal](18, 2) not null, --交易金额)insert into contractschedule(scheduleid,lineid,period,amount)values(89106,1,201507,90900.00),(89107,1,201508,9453.00),(89108,1,201510,13000.00),(89109,2,201501,12000.00),(89110,2,201503,11000.00),(89111,3,201509,9000.00),(89112,4,201510,8500.00)
3.补充其他（待） 
基础知识点：   1.for xml path  //用于统计时转换行列的格式，
   参考：王波洋老师的 灵活运用 for xml path
   2.pivot (sum(amount)) for period //用于基础表基础上的行列转换，
   参考：大志若愚老师的 纵表、横表互转的sql
   3.select sum(amount) from contractschedule
    group by lineid // 根据条件汇总数据
实现思路 
 逻辑 
/*计算时间的基础序列*/ ->/*格式化日期序列*/ -> /*关联逻辑表，查询计算8月份之前的汇总,8月份之后的按月份统计*/
代码片段
1 /*---------------计算时间的基础序列------------*/ 2 3 /*获取日期序列起始值*/
declare @sdate char(10);
declare @edate char(10);   4 set @sdate = '2015-08-01'--开始日期 5 set @edate = '2015-12-1' 6 7 /*存入临时表*/ 8 select * into #datearr 9 from (10 select 11 convert(varchar(6),dateadd(month,a.number,@sdate),112) totaldate12 from master..spt_values a --系统表13 where a.type = 'p' 14 and number between 0 and (select datediff(month,@sdate,@edate))15 )a16 17 select * from #datearr
1 /*格式化日期序列,用@months接收*/2 declare @months varchar(1000);3 declare @sql nvarchar(max);4 5 set @sql = 'select @months=stuff((select distinct '',[''+totaldate+'']'' from #datearr s6 for xml path('''')),1,1,'''')';7 execute sp_executesql @sql,n'@months varchar(1000) output',@months output;8 9 print @months
1 /*未关联时间序列前的基础数据*/ 2 with tab as( 3 select 4 c.contractcode 5 ,c.customname 6 ,cl.lineid 7 ,isnull(b.theendyearamount,0) as nearayearago 8 ,cs.amount 9 ,cs.period10 from contractinfo c11 left join12 contractline cl 13 on c.contractcode=cl.contractcode14 left join15 contractschedule cs16 on cs.lineid=cl.lineid17 --计算8月份之前的统计18 left join19 (20 select lineid,sum(amount) as theendyearamount21 from 22 contractschedule23 where period between 201508 and 20151224 group by lineid25 --select * from contractschedule26 )b on b.lineid=cl.lineid27 ) select * from tab
1 /*--------添加日期序列后的统计 --------*/ 2 set @sql=' 3 with tab as( 4 select c.customname 5 ,isnull(b.theendyearamount,0) as nearayearago 6 ,c.contractcode --合同号 7 ,cl.lineid --合同的行id 8 ,cs.amount --待计算的数量 9 ,cs.period --统计的日期10 from contractinfo c11 left join12 contractline cl 13 on c.contractcode=cl.contractcode14 left join15 contractschedule cs16 on cs.lineid=cl.lineid17 --计算8月份之前的统计18 left join19 (20 select lineid,sum(amount) as theendyearamount21 from 22 contractschedule23 where period between 201412 and 201508 24 group by lineid25 --select * from contractschedule26 )b on b.lineid=cl.lineid27 ) select * from tab28 pivot (sum(amount) for period29 in(30 '+@months+'31 ))b32 '33 exec (@sql)
查询后结果   脚本下载
思考
留下的思考1. 对空值的处理： select * from tab pivot (sum(amount)...
    这里我尝试用isnull（sum(amount)，0.00） 去处理，但语法没有通过，我将继续尝试..
2. 脚本片段中获取日期序列，或许在其他统计脚本中也会复用，我准备写到标量函数或表值函数中试一下。
3. 常用的业务统计脚本中关联的表比较多，如何能有效避免重复，在最后结果集中减少使用 distinct ，而使用group by 去过滤重复字段
这一个知识点我比较薄弱，不断总结，在分享经验给大家，少走弯路。
感谢    我的好朋友欢，一直致力于sql方面的统计，他给了我很多建议{
1.理解需求并开始写之前，要知道每个表里会出现什么数据
2.出现问题后，先查表与表之间是什么关联，关联从少到多，去检查错误
3.最核心的想清楚再写sql，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了 
  }
   博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。
   永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。