SQL按月统计

做统计就要和时间打交道。。。 

一：当前年的月统计

select b.number as [Month], sum(a.A4012) shouru,SUM(a.A4011) feiyong,sum(a.A4012-A4011) lirun
from A040A001 a 
right join 
(select number from master..spt_values where type='p' and number between 1 and 11) b
on B0110='00172' AND a.A4035>'2011-1-1' AND  month(a.A4035)=b.number 
group by b.number,year(a.A4035) ORDER BY [Month] 

结果：

 

二：时间段内按月统计

1.首先获得日期表

WITH date AS
(
 SELECT convert(char(7),DATEADD(month,number,'2011-07-01'),120) riqi
 FROM 
   master..spt_values 
 WHERE 
   type='P' 
 and 
   DATEADD(month,number,'2011-07-01')<='2012-02-01'
)
SELECT* FROM date

2.操作

WITH date AS
(
	SELECT convert(char(7),DATEADD(month,number,'2011-07-01'),120) riqi
	FROM 
	  master..spt_values 
	WHERE 
	  type='P' 
	and 
	  DATEADD(month,number,'2011-07-01')<='2012-02-01'
)
select date.riqi , sum(a.A4012) shouru,SUM(a.A4011) feiyong,sum(a.A4012-A4011) lirun
from A040A001 a 
right join 
date 
on B0110='00172' AND a.A4035>'2011-1-1' AND  convert(char(7),a.A4035,120)=date.riqi 
group by date.riqi