现有一表 有一时间列 时间是不连续的 例如a列 时间列
200 2010-5-5
300 2010-5-9
400 2010-5-10
求一SQL语句 或存储过程实现a列 时间列
200 2010-5-5
null 2010-5-6
null 2010-5-7
null 2010-5-8
300 2010-5-9
400 2010-5-10
200 2010-5-5
300 2010-5-9
400 2010-5-10
求一SQL语句 或存储过程实现a列 时间列
200 2010-5-5
null 2010-5-6
null 2010-5-7
null 2010-5-8
300 2010-5-9
400 2010-5-10
select dateadd(day,number,时间列) from master..spt_values b
where type='P' and dateadd(day,number,时间列) between (select min(时间列) from [Table]) and (select max(时间列) from [Table]) ) b
on b.时间列=a.时间列
use PracticeDB
go
if exists (select 1 from sysobjects where name ='tb')
drop table tb
go
create table tb(a列 int, 时间列 date )
insert into tb
select 200, '2010-5-5' union all
select 300, '2010-5-9' union all
select 400, '2010-5-10'select a列 ,dt as 时间列
from (select DATEADD(D,number,(select min(时间列) from tb)) dt
from master..spt_values b
where type='p' and
DATEADD(D,number,(select min(时间列) from tb)) between (select min(时间列) from tb) and (select max(时间列) from tb) )a
left join tb b on a.dt=b.时间列
a列 时间列
200 2010-05-05
NULL 2010-05-06
NULL 2010-05-07
NULL 2010-05-08
300 2010-05-09
400 2010-05-10
select dateadd(day,number,时间列)
这个时间列
还有
这个时间列
where type='P' and dateadd(day,number,时间列)
找不到啊
select convert(datetime, '2010-01-01') [day]
union all
select dateadd(d,1,day) from datelist where [day]<'2020-01-01'
)select * from datelist option (maxrecursion 32000);
Please supply sample data and exactly what outcome you expect