获得“零"对于没有记录的日期的计数 [英] Get "zero" for a count at dates without records

问题描述

所以我正在计算系统中用户的活动记录.我得到了某个月份和年份中每一天的活动计数器，就像后面的查询一样

So I'm counting activity records from users in my system. I get the activity counter for each day in a certain month and year, just like the query that follows

SELECT CONVERT(date, VIS_DATETIME) AS DATETIME, COUNT(*) AS ACTIVITY
FROM ACTIVITY
WHERE DATEPART(year, VIS_DATETIME) = 2016 AND
      DATEPART(month, VIS_DATETIME) = 3
GROUP BY CONVERT(date, VIS_DATETIME)
ORDER BY CONVERT(date, VIS_DATETIME)

问题是，如果比方说 3 月 28 日没有任何活动，它甚至不会被列出.但是，对于我的图表 API，我需要将其列出并使用 0 作为计数器.

The question is, if, let's say, March 28th doesn't have any activity, it won't be even listed. But, for my charts API, I need to get that listed and with a 0 for the counter.

显然，接受建议！

推荐答案

创建一个包含所有日期的表.然后对 Activity 表进行左连接.按日期分组，并对 Activity.id 执行 COUNT.左连接确保日期表中的所有日期都包含在结果集中，即使它们在连接子句中不匹配.

Create a table that contains all dates. Then do a left join with the Activity table. Group on the date, and do a COUNT on Activity.id. The left join ensures that all dates from the date table are included in the result set, even if they are not matched in the join clause.

这篇关于获得“零"对于没有记录的日期的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！