许多方法…在上一份工作中对许多应用程序来说对我来说效果很好的一种方法是建立时间表。
id - Year - Month - StartStamp - End Stamp
1 - 2008 - January - 1/1/2008 00:00:00.000 - 1/31/2008 23:59:59.999
然后,您可以将其日期字段放在startstamp和endstamp之间的timeframes表中进行联接。
这样可以很容易地拉出某个时间段或所有时间段…
您的记录可能更简单,只需12条记录(即1月1日)并加入DATEPART(m,DateColumn)
select mon.monthNumber, mon.monthName,
count(a.*) as count1, count(b.*) as count2
from months mon
left join whatever1 a on DATEPART(m,a.Date) = mon.monthNumber
left join whatever2 b on DATEPART(m,b.Date) = mon.monthNumber
group by mon.monthNumber, mon.monthName
您也可以临时执行月份操作,如下所示:
select mon.monthNumber, mon.monthName,
count(a.*) as count1, count(b.*) as count2
from (
select 1 as monthNumber, 'January' as monthName
union
select 2 as monthNumber, 'February' as monthName
union
select 3 as monthNumber, 'March' as monthName
union
......etc.....
) mon
left join whatever1 a on DATEPART(m,a.Date) = mon.monthNumber
left join whatever2 b on DATEPART(m,b.Date) = mon.monthNumber
group by mon.monthNumber, mon.monthName