求日期SQL,该怎么处理

2012-03-28

求日期SQL表原数据:datenum------------------2009-01-0112009-01-02102009-01-04122009-01-08202009-01-0

求日期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
[解决办法]

探讨

引用:
http://topic.csdn.net/u/20111101/16/7b219432-9f6d-45b8-81db-0456e242bfbe.html?83140

自己照着写

数据库不允许做这样的操作, 只能是查询, 建表是不可能让我怎么做的

[解决办法]
对应的时间按你的要求自己修改一下

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_timefrom    master..spt_values where     datediff(day,dateadd(day,number,@startdate), @enddate)>=0    and number>=0     and type='p') as Bleft join A ON A.[date] = B.day_time;

热点排行

SQL Server

求日期SQL,该怎么处理