如何在按日期段的查询中,在当天没有记录的情况下,插入为0的记录进结果集?
如题,
查询的范围是一个日期段,能得到如下结果:
date record
20060426 12260
20060630 39160
20060531 1400
20060623 9500
20060627 32440
20060214 740
20060628 59860
20060701 3040
但是我想要查询的结果为:
date record
20060426 12260
20060427 0
20060428 0
..............
请问如何修改我的查询呢?
select date,record from mytable where date > '查询日期条件1' and date < '查询日期条件2';
不胜感激,用存储过程或函数也可以,只要能解决这个问题..
问题点数：100、回复次数：5Top
1 楼LouisXIV（夜游神）回复于 2006-07-01 11:09:17 得分 0
先建一张日期空表再左联Top
2 楼Haiwer（海阔天空）回复于 2006-07-01 11:13:41 得分 0
补空日期
用临时表
Top
3 楼LouisXIV（夜游神）回复于 2006-07-01 11:17:52 得分 0
declare @calendar table
(
yyyymmdd varchar(8)
)
declare @startdate varchar(8)
declare @enddate varchar(8)
set @startdate='20060426'
set @enddate='20060701'
while @startdate<=@enddate
begin
insert into @calendar
select @startdate
set @startdate=convert(varchar,dateadd(day,1,@startdate),112)
end
select a.yyyymmdd,isnull(b.record,0) as Recode
(select * from @calendar)a
left join
(select [date],record from mytable where date between '20060426' and '20060701')b
on a.yyyymmdd=b.[date]
Top
4 楼paoluo（一天到晚游泳的鱼）回复于 2006-07-01 11:23:01 得分 0
Create Table TEST
([date] Varchar(10),
record Int)
Insert TEST Select '20060426', 12260
Union All Select '20060630', 39160
Union All Select '20060531', 1400
Union All Select '20060623', 9500
Union All Select '20060627', 32440
Union All Select '20060214', 740
Union All Select '20060628', 59860
Union All Select '20060701', 3040
GO
Select TOP 1000 ID=Identity(Int,1,1) Into # From Syscolumns,SysObjects
Declare @Mindate Varchar(10),@MaxDate Varchar(10)
Select @Mindate=Min([date]),@MaxDate=Max([date]) From TEST
Select Convert(Varchar,DateAdd(dd,ID-1,@Mindate),112) As [date] Into #T From # Where Convert(Varchar,DateAdd(dd,ID-1,@Mindate),112) <=@MaxDate
Select
A.[date],
IsNull(B.record,0) As record
From #T A
Left Join TEST B
On A.[date]=B.[date]
GO
Drop Table #,#t,TEST
--Result
/*
20060214 740
20060215 0
...
20060426 12260
...
20060531 1400
...
20060623 9500
20060624 0
20060625 0
20060626 0
20060627 32440
20060628 59860
20060629 0
20060630 39160
20060701 3040
*/Top
5 楼paoluo（一天到晚游泳的鱼）回复于 2006-07-01 11:23:53 得分 0
如果日期間隔小，用循環可以，間隔大的話，用循環，效率就差了。
Top
