mssql sqlserver 使用sql脚本统计数据时,生成多个统计列的方法分享


摘要:
下文讲述使用sum case 对数据进行分组统计,如下所示:
实验环境:sql server 2008 R2


例:
生成数据报表,统计出1号~30号每天的销售数量

   create table [maomao365.com] (busiDate  datetime,qty int)
   go  
   insert into [maomao365.com](busiDate,qty)values
   ('2019-6-1',5),('2019-6-3',8),('2019-6-1',89),('2019-6-12',98),
   ('2019-6-21',5),('2019-6-23',8),('2019-6-18',89),('2019-6-25',98)    
    go
    
  ---按月生成每天的数据报表如下所示:
     select  month(busiDate) as [月份],   
         sum(case when day(busiDate)=1 then qty else 0 end) as "1日销售额",  
         sum(case when day(busiDate)=2 then qty else 0 end) as "2日销售额", 
         sum(case when day(busiDate)=3 then qty else 0 end) as "3日销售额"  
    from [maomao365.com]
     where 1=1 
     group by    month(busiDate) 
     
    go
    truncate table [maomao365.com]
    drop       table [maomao365.com]