我需要时间段内（如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