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 

查詢結果如下：