SQL计数以包括零值 [英] SQL Count to include zero values

问题描述

我已创建以下存储过程，用于计算选定位置的特定范围之间每天的记录数：

I have created the following stored procedure that is used to count the number of records per day between a specific range for a selected location:

[dbo].[getRecordsCount] 
@LOCATION as INT,
@BEGIN as datetime,
@END as datetime

SELECT
ISNULL(COUNT(*), 0) AS counted_leads, 
CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)), 3) as TIME_STAMP 
FROM HL_Logs
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp))

但问题是结果不显示有零记录的日子，我确定它与我的WHERE语句有关，不允许零值

but the problem is that the result does not show the days where there are zero records, I pretty sure that it has something to do with my WHERE statement not allowing the zero values to be shown but I do not know how to over come this issue.

提前感谢
Neil

Thanks in advance Neil

推荐答案

不是那么多的WHERE子句，而是GROUP BY。简单地说，查询将只返回存在的行的数据。这意味着，当您按时间戳的日期进行分组时，将只返回有行的日期。 SQL Server不能从上下文知道你想填空，它不会知道是什么。

Not so much the WHERE clause, but the GROUP BY. Simply put, the query will only return data for rows that exist. That means when you're grouping by the date of the timestamp, only days for which there are rows will be returned. SQL Server can't know from context that you want to "fill in the blanks", and it wouldn't know what with.

正常的答案是CTE产生你想看到的所有天，从而填补空白。这个有点棘手，因为它需要一个递归的SQL语句，但它是一个众所周知的技巧：

The normal answer is a CTE that produces all the days you want to see, thus filling in the blanks. This one's a little tricky because it requires a recursive SQL statement, but it's a well-known trick:

WITH CTE_Dates AS
(
    SELECT @START AS cte_date
    UNION ALL
    SELECT DATEADD(DAY, 1, cte_date)
    FROM CTE_Dates
    WHERE DATEADD(DAY, 1, cte_date) <= @END
)
SELECT
cte_date as TIME_STAMP,
ISNULL(COUNT(*), 0) AS counted_leads, 
FROM CTE_Dates
LEFT JOIN HL_Logs ON DATEADD(dd, 0, DATEDIFF(dd, 0, Time_Stamp)) = cte_date
WHERE Time_Stamp between @BEGIN and @END and ID_Location = @LOCATION
GROUP BY cte_date

打破它，CTE使用一个引用自身的联合，递归地一次添加到上一个日期，日期作为表的一部分。如果你运行一个使用CTE的简单语句，并且只是从它选择*，你会看到一个开始和结束之间的日期列表。然后，语句根据日志时间戳日期将此日期列表加入日志表，同时保留没有使用左连接的日志条目的日期（从左侧获取所有行，是否在右侧）。最后，我们按日期和计数分组，我们应该得到你想要的答案。

Breaking it down, the CTE uses a union that references itself to recursively add one day at a time to the previous date and remember that date as part of the table. If you ran a simple statement that used the CTE and just selected * from it, you'd see a list of dates between start and end. Then, the statement joins this list of dates to the log table based on the log timestamp date, while preserving dates that have no log entries using the left join (takes all rows from the "left" side whether they have matching rows on the "right" side or not). Finally, we group by date and count instead and we should get the answer you want.

这篇关于SQL计数以包括零值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！