统计每天订单量,即使没有,也会自动填充0的处理

需求很简单,就是统计一年以来每天的订单量,一开始以为很简单,group by 日期下就好了,

但发现很多天的订单量是0,于是那天就没有统计进去,,如果那天没有订单,那也要显示出来,日期,订单量(0).

具体实现:这里需要一个参照表，这个表最少包含一列连续的没有截断的日期.

下面是创建的时间表:

CREATE   TABLE   [dbo].[time_dimension]   (      [time_id]   [int]   IDENTITY   (1,   1)   NOT   NULL   ,      [the_date]   [datetime]   NULL   ,      [the_day]   [nvarchar]   (15)   NULL   ,      [the_month]   [nvarchar]   (15)   NULL   ,      [the_year]   [smallint]   NULL   ,      [day_of_month]   [smallint]   NULL   ,      [week_of_year]   [smallint]   NULL   ,      [month_of_year]   [smallint]   NULL   ,      [quarter]   [nvarchar]   (2)   NULL   ,      [fiscal_period]   [nvarchar]   (20)   NULL)   ON   [PRIMARY]<br>    DECLARE   @WeekString   varchar(12),@dDate   SMALLDATETIME,@sMonth   varchar(20),@iYear   smallint,@iDayOfMonth   smallint,@iWeekOfYear   smallint,@iMonthOfYear   smallint,@sQuarter   varchar(2),@sSQL   varchar(100),@adddays   int    SELECT   @adddays   =   1   --日期增量（可以自由设定）SELECT   @dDate   =   '01/01/2002 '   --开始日期    WHILE   @dDate   <   '12/31/2004 '     --结束日期BEGIN          SELECT   @WeekString   =   DATENAME   (dw,   @dDate)      SELECT   @sMonth=DATENAME(mm,@dDate)      SELECT   @iYear=   DATENAME   (yy,   @dDate)      SELECT   @iDayOfMonth=DATENAME   (dd,   @dDate)      SELECT   @iWeekOfYear=   DATENAME   (week,   @dDate)      SELECT   @iMonthOfYear=DATEPART(month,   @dDate)      SELECT   @sQuarter   =   'Q '   +     CAST(DATENAME   (quarter,   @dDate)as   varchar(1))<br>      INSERT   INTO   time_dimension(the_date,   the_day,   the_month,   the_year,      day_of_month,      week_of_year,   month_of_year,   quarter)   VALUES      (@dDate,   @WeekString,   @sMonth,   @iYear,   @iDayOfMonth,   @iWeekOfYear,      @iMonthOfYear,   @sQuarter)      SELECT   @dDate   =   @dDate   +   @adddaysENDGO

然后和订单表做一个左连接,

select a.日期,isnull(b.订单量,0) 订单量
from a left join (订单表) b on a.日期=b.日期