提示: 自己定義一個表TB1,從2010-07-01 到 2010-07-10每天都有一條數據 如: todate 2010-07-01 2010-07-02 2010-07-03 ... 2010-07-10 然後用你的日誌表TB2關聯 select B.* FROM TB1 A LEFT JOIN TB2 B ON A.TODATE=B.ADDDATE WHERE ADDDATE BETWEEN STARTDATE1 AND ENDDATE
declare @ldt_begin datetime,@ldt_end datetime declare @lt_dt table(ldt_date datetime) declare @ldt_tmp datetime set @ldt_begin='2010-07-01' set @ldt_end='2010-07-10'set @ldt_tmp=@ldt_beginwhile @ldt_tmp<=@ldt_end begin insert into @lt_dt(ldt_date) select @ldt_tmp set @ldt_tmp=dateadd(day,1,@ldt_tmp) endselect * from @lt_dt2010-07-01 00:00:00.000 2010-07-02 00:00:00.000 2010-07-03 00:00:00.000 2010-07-04 00:00:00.000 2010-07-05 00:00:00.000 2010-07-06 00:00:00.000 2010-07-07 00:00:00.000 2010-07-08 00:00:00.000 2010-07-09 00:00:00.000 2010-07-10 00:00:00.000select a.id,isnull(a.addtime,b.ldt_date),a.log from 表 a right join @lt_dt b on a.addtime=b.ldt_date where a.addtime>=@ldt_begin and a.addtime<=@ldt_end测试看一下。
自己定義一個表TB1,從2010-07-01 到 2010-07-10每天都有一條數據
如:
todate
2010-07-01
2010-07-02
2010-07-03
...
2010-07-10
然後用你的日誌表TB2關聯
select B.* FROM TB1 A LEFT JOIN TB2 B ON A.TODATE=B.ADDDATE WHERE ADDDATE BETWEEN STARTDATE1 AND ENDDATE
http://blog.csdn.net/xys_777/archive/2010/06/20/5681208.aspx
declare @ldt_begin datetime,@ldt_end datetime
declare @lt_dt table(ldt_date datetime)
declare @ldt_tmp datetime
set @ldt_begin='2010-07-01'
set @ldt_end='2010-07-10'set @ldt_tmp=@ldt_beginwhile @ldt_tmp<=@ldt_end
begin
insert into @lt_dt(ldt_date) select @ldt_tmp
set @ldt_tmp=dateadd(day,1,@ldt_tmp)
endselect * from @lt_dt2010-07-01 00:00:00.000
2010-07-02 00:00:00.000
2010-07-03 00:00:00.000
2010-07-04 00:00:00.000
2010-07-05 00:00:00.000
2010-07-06 00:00:00.000
2010-07-07 00:00:00.000
2010-07-08 00:00:00.000
2010-07-09 00:00:00.000
2010-07-10 00:00:00.000select a.id,isnull(a.addtime,b.ldt_date),a.log
from 表 a
right join @lt_dt b
on a.addtime=b.ldt_date
where a.addtime>=@ldt_begin and a.addtime<=@ldt_end测试看一下。
left join 出来后的数据也是不完整呀
49,0,2010-05-27 00:00:00.000
549,0,2010-05-28 00:00:00.000
549,0,2010-05-29 00:00:00.000
549,0,2010-05-30 00:00:00.000
549,0,2010-05-31 00:00:00.000
214,15,2010-05-01 00:00:00.000
214,15,2010-05-02 00:00:00.000
214,15,2010-05-03 00:00:00.000
214,15,2010-05-04 00:00:00.000
省略完整数据
214,15,2010-05-28 00:00:00.000
214,15,2010-05-29 00:00:00.000
213,3,2010-05-01 00:00:00.000
213,3,2010-05-02 00:00:00.000
有的可以正常31号 有的只到29号就没有了