对应的时间按你的要求自己修改一下 use tempdb; /* create table A ( [date] date not null, [num] int ); insert into A values ('2009-01-01',1), ('2009-01-02',10), ('2009-01-04',12), ('2009-01-08',20), ('2009-01-09',0), ('2009-01-10',1); */declare @startdate datetime,@enddate datetime; set @startdate='2009-01-01'; set @enddate='2009-01-31'; select B.day_time,ISNULL(A.num,0) as num from ( select convert(varchar(10),dateadd(day,number,@startdate),120) as day_time from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p' ) as B left join A ON A.[date] = B.day_time;
master..spt_values是系统表,不是你创建的,只是作为辅助功能而已。
SELECT A.trddt,B.new_date FROM STK_Dlyr A right join (select cast( '1989-09-01' as datetime) +number as new_date from master..spt_values where type='P' ) B on A.trddt =B.new_date where a.stkcd='000001' 结果: trddt new_date ---------- ----------- 1991-04-03 1991-04-03 1991-04-04 1991-04-04 1991-04-05 1991-04-05 1991-04-06 1991-04-06 1991-04-08 1991-04-08 1991-04-09 1991-04-09 1991-04-10 1991-04-10 1991-04-11 1991-04-11 1991-04-12 1991-04-12 还是没有7号的
A LEFT JOIN TB
use tempdb;
/*
create table A
(
[date] date not null,
[num] int
);
insert into A values
('2009-01-01',1),
('2009-01-02',10),
('2009-01-04',12),
('2009-01-08',20),
('2009-01-09',0),
('2009-01-10',1);
*/declare @startdate datetime,@enddate datetime;
set @startdate='2009-01-01';
set @enddate='2009-01-31';
select B.day_time,ISNULL(A.num,0) as num from
(
select convert(varchar(10),dateadd(day,number,@startdate),120) as day_time
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>=0
and type='p'
) as B
left join A
ON A.[date] = B.day_time;
SELECT A.trddt,B.new_date FROM STK_Dlyr A right join (select cast( '1989-09-01' as datetime) +number as new_date from master..spt_values where type='P' ) B
on A.trddt =B.new_date
where a.stkcd='000001'
结果:
trddt new_date
---------- -----------
1991-04-03 1991-04-03
1991-04-04 1991-04-04
1991-04-05 1991-04-05
1991-04-06 1991-04-06
1991-04-08 1991-04-08
1991-04-09 1991-04-09
1991-04-10 1991-04-10
1991-04-11 1991-04-11
1991-04-12 1991-04-12
还是没有7号的