22,209
社区成员
发帖
与我相关
我的任务
分享
--创建表
--create table [table](dtime datetime, data varchar(10), num smallint)
--添加测试数据
--insert into [table] select '2002-9-8' ,'data1',0
--insert into [table] select '2002-9-10' ,'data1',3
--insert into [table] select '2002-9-11' ,'data1',4
--测试语句
select * from [table]
--你需要的效果
declare @d table(time datetime)
declare @date datetime
set @date='2002-09-07'
while @date<='2002-09-12'
begin
insert @d select @date
set @date= dateadd(dd,1,cast(@date as datetime))
end
select convert(varchar(10),time,120) as time
, isnull(data, 'data1') as data
,isnull(num, 0) as num
from @d d left join [table] t
on convert(varchar(10),dtime,120) = convert(varchar(10),time,120)
http://zhidao.baidu.com/link?url=sG41OMsJInPrcqFWdZe_g3dwGMKm25-lPKS_uk2LzzBTNPJM9870rDWgUpkwRaq_ji1VSmNLwKeM-jAk22-UwAtMPsmHbD9UrNp7G4f4TQG[/quote]
貌似只能用存储过程了
--创建表
--create table [table](dtime datetime, data varchar(10), num smallint)
--添加测试数据
--insert into [table] select '2002-9-8' ,'data1',0
--insert into [table] select '2002-9-10' ,'data1',3
--insert into [table] select '2002-9-11' ,'data1',4
--测试语句
select * from [table]
--你需要的效果
declare @d table(time datetime)
declare @date datetime
set @date='2002-09-07'
while @date<='2002-09-12'
begin
insert @d select @date
set @date= dateadd(dd,1,cast(@date as datetime))
end
select convert(varchar(10),time,120) as time
, isnull(data, 'data1') as data
,isnull(num, 0) as num
from @d d left join [table] t
on convert(varchar(10),dtime,120) = convert(varchar(10),time,120)
http://zhidao.baidu.com/link?url=sG41OMsJInPrcqFWdZe_g3dwGMKm25-lPKS_uk2LzzBTNPJM9870rDWgUpkwRaq_ji1VSmNLwKeM-jAk22-UwAtMPsmHbD9UrNp7G4f4TQG
declare @dt1 date
declare @dt2 date
set @dt1 = '2015-06-28'
set @dt2 = '2015-07-05'
;with cte
as(
select @dt1 as dt
union all
select dateadd(day, 1, T1.dt) as dt from cte T1
where not exists
(select dt from
(select @dt1 as dt) as T2
where T2.dt=DATEADD(DD,1,T1.dt)
)
and T1.dt < @dt2
)
select * from cte
/*---------------------------------------------------*/
dt
2015-06-28
2015-06-29
2015-06-30
2015-07-01
2015-07-02
2015-07-03
2015-07-04
2015-07-05