连续日期数据补全，求帮助解决方案

连续日期数据补全，求帮助

我需要时间段内（如2012-01-01至2012-01-04）每天的aaa bbb ccc数据（每天3条）  
即需要查询结果table1补全如table2，即 即使当天没有数据也要补全并赋值为NULL 谢谢！

[table 1]
  name date value
-----------------------------------
  aaa 2012-01-01 1
  bbb 2012-01-01 1
  aaa 2012-01-02 1
  bbb 2012-01-02 1
  ccc 2012-01-02 1
  aaa 2012-01-03 1
  bbb 2012-01-03 1
  aaa 2012-01-04 1
  bbb 2012-01-04 1


[table 2]
  name date value
-----------------------------------
  aaa 2012-01-01 1
  bbb 2012-01-01 1
  ccc 2012-01-01 NULL
  aaa 2012-01-02 1
  bbb 2012-01-02 1
  ccc 2012-01-02 1
  aaa 2012-01-03 1
  bbb 2012-01-03 1
  ccc 2012-01-03 NULL
  aaa 2012-01-04 1
  bbb 2012-01-04 1
  ccc 2012-01-04 0

[解决办法]
生成连续时间参考http://blog.csdn.net/geniuswjt/article/details/6872647

SQL code

create table t1(name varchar(10),[date] datetime,value int)insert t1select 'aaa', '2012-01-01', 1 union allselect 'bbb', '2012-01-01', 2 union allselect 'aaa', '2012-01-02', 3 union allselect 'bbb', '2012-01-02', 4 union allselect 'ccc', '2012-01-02', 5 union allselect 'aaa', '2012-01-03', 6 union allselect 'bbb', '2012-01-03', 7 union allselect 'aaa', '2012-01-04', 8 union allselect 'bbb', '2012-01-04', 9go;with cte as(select name='aaa',[date],vaule=null from t1union allselect name='bbb',[date],vaule=null from t1 union allselect name='ccc',[date],vaule=null from t1union allselect * from t1)select name,[date],value=MAX(vaule) from cte agroup by name,[date]/*name  date                vaule---    ---                 ----aaa    2012-01-01 00:00:00.000    1bbb    2012-01-01 00:00:00.000    2ccc    2012-01-01 00:00:00.000    NULLaaa    2012-01-02 00:00:00.000    3bbb    2012-01-02 00:00:00.000    4ccc    2012-01-02 00:00:00.000    5aaa    2012-01-03 00:00:00.000    6bbb    2012-01-03 00:00:00.000    7ccc    2012-01-03 00:00:00.000    NULLaaa    2012-01-04 00:00:00.000    8bbb    2012-01-04 00:00:00.000    9ccc    2012-01-04 00:00:00.000    NULL*/godrop table t1