日期范围查询问题

[海棠无香1998]

declare @beginTime datetime;
declare @endTime datetime;

declare @beginHH varchar(32);
declare @endHH varchar(32);

set @beginTime = '2013-06-30';
set @beginHH = '06:00:00';
set @endTime = '2013-07-05';
set @endHH = '06:59:59';

SELECT CONVERT(char(10), [RegTime],126) as '统计时间', count([ID]) as '数量'
FROM [Datagram].[dbo].[RegisterT]
where convert(char(8),[RegTime],108) between @beginHH and @endHH
and [RegTime] between @beginTime and @endTime
group by  CONVERT(char(10), [RegTime], 126)

结果：

统计时间        数量
2013-07-01	1
2013-07-02	4
2013-07-03	1
2013-07-04	1

如何将2013-06-30 和 2013-07-05的数据，没有的话 ，在查询结果中 显示为0
如想要的结果为：

统计时间        数量
2013-06-30      0
2013-07-01	1
2013-07-02	4
2013-07-03	1
2013-07-04	1
2013-07-05      0

在线等，求大神！

[KevinLiu]

你先做一张时间表包含所有时间然后LEFT JOIN现在的结果，如果没有值就显示为0

[海棠无香1998]

引用 1 楼 SmithLiu328 的回复:

你先做一张时间表包含所有时间然后LEFT JOIN现在的结果，如果没有值就显示为0

大神，那个临时表怎么写啊？

[KevinLiu]

你不是有开始时间和结束时间嘛，循环一下用DATEADD函数每次加一天直到END时间这样就可以做一张时间表了。

[lzw_0736]

--時間临时表： declare @startDate datetime,@endDate datetime,@numDays int select @startDate = '2013-06-01',@endDate = '2013-06-30' set @numDays = datediff(day, @startDate, @endDate) + 1; With NumDays as ( select top(@numDays) row_number() over(order by (select 0)) as n from sys.objects o1, sys.objects o2 ) select convert(varchar(10), dateadd(day, NumDays.n - 1, @startDate), 120) as date from NumDays;

[Leon_He2014]

declare @beginTime datetime;
declare @endTime datetime;

declare @beginHH varchar(32);
declare @endHH varchar(32);

set @beginTime = '2013-06-30';
set @beginHH = '06:00:00';
set @endTime = '2013-07-05';
set @endHH = '06:59:59';

declare @DateNum int=datediff(d,@beginTime,@endTime);

--select @DateNum

;with t as
(
select convert(char(10), dateadd(d,number,@beginTime),126) EventDate
from master..spt_values
where [type]='p'
and number<=@DateNum
)
select t.EventDate,isnull(数量,0) as [数量]
from t 
left join
(
SELECT CONVERT(char(10), [RegTime],126) as '统计时间', count([ID]) as '数量'
FROM [Datagram].[dbo].[RegisterT]
where convert(char(8),[RegTime],108) between @beginHH and @endHH
and [RegTime] between @beginTime and @endTime
group by  CONVERT(char(10), [RegTime], 126)
) t1
on t.EventDate=t1.统计时间

[海棠无香1998]

引用 5 楼 stublue 的回复:

declare @beginTime datetime;
declare @endTime datetime;

declare @beginHH varchar(32);
declare @endHH varchar(32);

set @beginTime = '2013-06-30';
set @beginHH = '06:00:00';
set @endTime = '2013-07-05';
set @endHH = '06:59:59';

declare @DateNum int=datediff(d,@beginTime,@endTime);

--select @DateNum

;with t as
(
select convert(char(10), dateadd(d,number,@beginTime),126) EventDate
from master..spt_values
where [type]='p'
and number<=@DateNum
)
select t.EventDate,isnull(数量,0) as [数量]
from t 
left join
(
SELECT CONVERT(char(10), [RegTime],126) as '统计时间', count([ID]) as '数量'
FROM [Datagram].[dbo].[RegisterT]
where convert(char(8),[RegTime],108) between @beginHH and @endHH
and [RegTime] between @beginTime and @endTime
group by  CONVERT(char(10), [RegTime], 126)
) t1
on t.EventDate=t1.统计时间

hi,我改了部分代码，如下

declare @beginTime datetime;
declare @endTime datetime;

declare @beginHH varchar(32);
declare @endHH varchar(32);

set @beginTime = '2013-06-30';
set @beginHH = '06:00:00';
set @endTime = '2013-07-05';
set @endHH = '06:59:59';


WITH t AS (
	SELECT @beginTime AS 'EventDate'
	UNION ALL
	SELECT DATEADD(DAY,1,EventDate) FROM tb WHERE EventDate<@endTime
)


select t.EventDate,isnull(数量,0) as [数量]
from t 
left join
(
SELECT CONVERT(char(10), [RegTime],126) as '统计时间', count([ID]) as '数量'
FROM [Datagram].[dbo].[RegisterT]
where convert(char(8),[RegTime],108) between @beginHH and @endHH
and [RegTime] between @beginTime and @endTime
group by  CONVERT(char(10), [RegTime], 126)
) t1
on t.EventDate=t1.统计时间

[KevinLiu]

都是代码牛人哦