原文:
sql server 查询本年的每个月的数据
一、以一行数据的形式，显示本年的12月的数据，本示例以2017年为例，根据CreateDate字段判断，计算总和，查询语句如下：
select sum(case when datepart(month,CreateDate)=1 then 1 else 0 end) as '1月', sum(case when datepart(month,CreateDate)=2 then 1 else 0 end) as '2月', sum(case when datepart(month,CreateDate)=3 then 1 else 0 end) as '3月', sum(case when datepart(month,CreateDate)=4 then 1 else 0 end) as '4月', sum(case when datepart(month,CreateDate)=5 then 1 else 0 end) as '5月', sum(case when datepart(month,CreateDate)=6 then 1 else 0 end) as '6月', sum(case when datepart(month,CreateDate)=7 then 1 else 0 end) as '7月', sum(case when datepart(month,CreateDate)=8 then 1 else 0 end) as '8月', sum(case when datepart(month,CreateDate)=9 then 1 else 0 end) as '9月', sum(case when datepart(month,CreateDate)=10 then 1 else 0 end) as '10月', sum(case when datepart(month,CreateDate)=11 then 1 else 0 end) as '11月', sum(case when datepart(month,CreateDate)=12 then 1 else 0 end) as '12月' from MO_Members where datepart(year,CreateDate)='2017'
查询结果如下：
二、根据当前日期，以列的数据形式，显示本年的12个月的数据，查询语句如下：
select date=convert(varchar(10),dateadd(month, 0,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,1,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,2,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,3,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,4,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,5,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,6,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,7,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,8,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,9,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,10,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120) union all select date=convert(varchar(10),dateadd(month,11,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120)
查询结果如下：
二、具体应用示例：以2017为例，查询语句如下：
with t as ( select date=substring(convert(varchar(10),dateadd(month, 0,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,1,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,2,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,3,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,4,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,5,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,6,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,7,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,8,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,9,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,10,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) union all select date=substring(convert(varchar(10),dateadd(month,11,convert(varchar(10),DATEADD(year, DATEDIFF(year,0,getdate()), 0),120)),120),6,2) ) select id=ROW_NUMBER()OVER(ORDER BY t1.date), date=t1.date+'月', Counts=sum(isnull(t2.counts,0)) from t t1 left join ( select substring(convert(varchar,CreateDate,120),6,2) as CreateDate,count(*) as counts from MO_Members where datepart(year,CreateDate)='2017' group by substring(convert(varchar,CreateDate,120),6,2) ) t2 on t1.date= CreateDate group by t1.date
查询结果如下：
