包含零值的 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.

提前致谢尼尔

推荐答案

与其说是 WHERE 子句，不如说是 GROUP BY.查询将只返回存在的行的数据.这意味着当您按时间戳的日期分组时，只会返回有行的天数.SQL Server 无法从上下文中知道您要填空"，它也不知道用什么.

Not so much the WHERE clause, but the GROUP BY. 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(HL_Logs.Time_Stamp), 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屋！