SQL返回日期范围，即使count为null
SQL返回日期范围，即使count为null
提问于 2013-03-06 22:34:24
我正在计算过去7天内的登录总数(UsersID)。我对此日期范围内的用户id的总数进行了计数，并按日期分组。我的问题是，如果没有记录日期的用户id，则不会显示该日期。
所以我需要为没有登录的日期显示0。
我的代码是：
SET @StartDate = CONVERT(varchar(10),DATEADD(day,-7, GETDATE()), 101) + ' 00:00.00';
SET @EndDate = DATEADD(day,7, @StartDate);
SELECT CONVERT(DATE, [DateTime]) as LoginDate, COUNT(UsersID) AS TotalForDay
FROM [STATS].[dbo].[UsersLogin]
WHERE [DateTime]>@StartDate
AND [DateTime]<@EndDate
GROUP BY CONVERT(DATE, [DateTime])
ORDER BY LoginDate desc;回答 3
Stack Overflow用户
回答已采纳
发布于 2013-03-06 22:40:28
您需要一个带有日期值的单独的表，试试这个
DECLARE @startdate datetime, @enddate datetime
SET @StartDate = CONVERT(varchar(10),DATEADD(day,-7, GETDATE()), 101) + ' 00:00.00';
SET @EndDate = DATEADD(day,7, @StartDate);
;with cte as
(
select @startdate DateValue
union all
select DateValue + 1
from cte
where DateValue + 1 < @enddate
)
select a.DateValue as LoginDate, COUNT(UsersID) AS TotalForDay
from cte a
LEFT outer join [STATS].[dbo].[UsersLogin] b
on CONVERT(varchar(15), a.DateValue,101) = CONVERT(varchar(15), b.[DateTime],101)
group by a.DateValue
order by a.DateValue desc对于按小时分组：
DECLARE @startdate datetime, @enddate datetime
SET @StartDate = CONVERT(varchar(10),DATEADD(day,-7, GETDATE()), 101) + ' 00:00.00';
SET @EndDate = DATEADD(day,7, @StartDate);
;with cte as
(
select @startdate DateValue
union all
select DATEADD (hh,1,DateValue)
from cte
where DATEADD (hh,1,DateValue) < @enddate
)
select a.DateValue as LoginDate, COUNT(UsersID) AS TotalForDay
from cte a
LEFT outer join [STATS].[dbo].[UsersLogin] b
on CONVERT(varchar(15), a.DateValue,101) = CONVERT(varchar(15), b.[DateTime],101)
group by a.DateValue
order by a.DateValue desc
OPTION (MAXRECURSION 0) Stack Overflow用户
发布于 2013-03-06 22:41:08
无论是静态实现为表还是动态实现为子查询，您都需要对Calendar关系执行左连接。
SET @StartDate = CONVERT(varchar(10),DATEADD(day,-7, GETDATE()), 101) + ' 00:00.00';
SET @EndDate = DATEADD(day,7, @StartDate);
SELECT C.Date, COUNT(UsersID) AS TotalForDay
FROM Calendar as C
LEFT JOIN [STATS].[dbo].[UsersLogin] as U on C.Date = CONVERT(DATE, [DateTime])
WHERE [DateTime]>@StartDate
AND [DateTime]<@EndDate
GROUP BY CONVERT(DATE, [DateTime])
ORDER BY LoginDate desc;Stack Overflow用户
发布于 2013-03-06 22:58:41
如果需要比使用这个更简单的方法。创建一个临时表变量，并在其中添加从头到尾的日期：
Declare @temp_Dates Table
(
TempDate datetime
)
While (@StartDate<@EndDate)
Begin
Insert Into @temp_Dates Select @EndDate
Set @EndDate=DATEADD(DAY,-1,@EndDate)
End在那之后，将你的表与上面的表进行左连接：
SELECT Cast(Convert(varchar(20),td.TempDate,101) as DateTime) as LoginDate, COUNT(UsersID) AS TotalForDay
FROM @temp_Dates td
Left Join UsersLogin ul On Cast(Convert(varchar(20),ul.DateTime,101) as DateTime)=Cast(Convert(varchar(20),td.TempDate,101) as DateTime)
GROUP BY Cast(Convert(varchar(20),td.TempDate,101) as DateTime)
ORDER BY LoginDate desc你会得到你想要的结果。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/15250088
复制相关文章
相似问题
腾讯云开发者
