求日期SQL,该怎么处理
求日期SQL
表原数据:
date num
------------------
2009-01-01 1
2009-01-02 10
2009-01-04 12
2009-01-08 20
2009-01-09 0
2009-01-10 1
我要出现查询结果是
date num
------------------
2009-01-01 1
2009-01-02 10
2009-01-03 0
2009-01-04 12
2009-01-05 0
2009-01-06 0
2009-01-07 0
2009-01-08 20
2009-01-09 0
2009-01-10 1
会出现跨月 年
------解决方案--------------------
生成一个日期表 A
A LEFT JOIN TB
------解决方案--------------------
master..spt_values构造时间表 与你的表left join
------解决方案--------------------
------解决方案--------------------
对应的时间按你的要求自己修改一下
表原数据:
date num
------------------
2009-01-01 1
2009-01-02 10
2009-01-04 12
2009-01-08 20
2009-01-09 0
2009-01-10 1
我要出现查询结果是
date num
------------------
2009-01-01 1
2009-01-02 10
2009-01-03 0
2009-01-04 12
2009-01-05 0
2009-01-06 0
2009-01-07 0
2009-01-08 20
2009-01-09 0
2009-01-10 1
会出现跨月 年
------解决方案--------------------
生成一个日期表 A
A LEFT JOIN TB
------解决方案--------------------
master..spt_values构造时间表 与你的表left join
------解决方案--------------------
------解决方案--------------------
对应的时间按你的要求自己修改一下
- SQL code
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;