连续日期数据补全，求帮助解决方案
日期：2014-05-18 浏览次数：20765 次
连续日期数据补全，求帮助
我需要时间段内（如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
------解决方案--------------------
是这样吗?
我需要时间段内（如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 t1
select 'aaa', '2012-01-01', 1 union all
select 'bbb', '2012-01-01', 2 union all
select 'aaa', '2012-01-02', 3 union all
select 'bbb', '2012-01-02', 4 union all
select 'ccc', '2012-01-02', 5 union all
select 'aaa', '2012-01-03', 6 union all
select 'bbb', '2012-01-03', 7 union all
select 'aaa', '2012-01-04', 8 union all
select 'bbb', '2012-01-04', 9
go
;with cte as(
select name='aaa',[date],vaule=null from t1
union all
select name='bbb',[date],vaule=null from t1
union all
select name='ccc',[date],vaule=null from t1
union all
select * from t1
)
select name,[date],value=MAX(vaule) from cte a
group by name,[date]
/*
name date vaule
--- --- ----
aaa 2012-01-01 00:00:00.000 1
bbb 2012-01-01 00:00:00.000 2
ccc 2012-01-01 00:00:00.000 NULL
aaa 2012-01-02 00:00:00.000 3
bbb 2012-01-02 00:00:00.000 4
ccc 2012-01-02 00:00:00.000 5
aaa 2012-01-03 00:00:00.000 6
bbb 2012-01-03 00:00:00.000 7
ccc 2012-01-03 00:00:00.000 NULL
aaa 2012-01-04 00:00:00.000 8
bbb 2012-01-04 00:00:00.000 9
ccc 2012-01-04 00:00:00.000 NULL
*/
go
drop table t1
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
