对于两个日期之间的月总数返回零，即使这些月份没有数据存在

Question

我有以下查询，它返回按月和年分组的事务表的总数:

SELECT
DATEPART(month, T.Date) AS Month,
DATEPART(year, T.Date) AS Year,
ISNULL(SUM(Amount) ,0) AS Total
FROM
Transactions AS T
GROUP BY 
DATEPART(month, T.Date), DATEPART(year, T.Date)

此查询将只返回事务表中有当月和当年事务的那些月份的月总数。

我如何修改上面的查询，使它在两个日期之间的所有月份和年份返回0 ?

我怀疑我需要交叉引用上面的查询与需要动态构建包含所有月份和年份的表，但我不确定如何去做。

Answer 1

如果只需要月份和年份，实际上不需要额外的表。您可以使用递归CTE(公共表表达式)模拟一个。

DECLARE @startDate datetime = '1/1/2020';
DECLARE @endDate datetime = '1/1/2023';
-- This is a recursive Common Table Expression. They allow you to generate
-- sets of records from seemingly nothing. 
-- A recursive CTE involves two SELECT statements joined with a UNION ALL statement. 
-- The first statement is the "anchor". It is usually the start of the sequence
-- you're trying to generate. In our case, we say "select our start date
-- as the first record in the sequence we're generating".
-- The second SELECT statement refers recursively to the first one. (I
-- think it may be called "the generator clause", but I'm not sure.) We
-- refer to the record or records selected in the first statement, but
-- add or modify the date to produce the second and subsequent records.
-- So, in our case, we're saying, "Select record(s) from the CTE, and 
-- this time, derive our value ("ReferenceDate") by adding a month to
-- the value we're reading." 
-- This will continue indefinitely, if you query it. That's why we need
-- a test (the WHERE clause) to limit the records we return, or you'll
-- quickly reach the year 10,000!
WITH Calendar AS (
SELECT @startDate as ReferenceDate
UNION ALL
SELECT DateAdd(month, 1, c.ReferenceDate)
FROM   Calendar c
WHERE  c.ReferenceDate < @endDate
), 
-- The Calendar CTE is a list of the first date of every month. But
-- for our report we need the year and the month, separately. So,
-- let's produce a second CTE (akin to a derived table) that returns
-- the year and month for every date in Calendar.
YearMonths AS (
SELECT datepart(year, ReferenceDate) as [Year]
,   datepart(month, ReferenceDate) as [Month]
FROM   Calendar
),
-- This CTE is your summary data. We're going to want to join it
-- to YearMonths, and we need it already summarized. 
-- (We could have done this as a derived table in the final select,
-- but IMO that would have complicated it without giving any advantage.
-- CTEs perform like subqueries or derived tables.)
SummaryTransactions AS (
select
DATEPART(month, T.Date) as Month,
DATEPART(year, T.Date) as Year,
ISNULL(SUM(Amount) ,0) As Total
from Transactions AS T
GROUP BY 
DATEPART(month, T.Date), 
DATEPART(year, T.Date)
)
-- The final query!
SELECT ym.Year
,   ym.Month
,   ISNULL(st.Total, 0) as Total
FROM   YearMonths ym
LEFT JOIN 
SummaryTransactions st
ON ym.Year = st.Year
AND ym.Month = st.Month

这是我的记忆，所以我不能保证它没有打字错误，但它应该可以工作。

CalendarCTE作为内存中的表;它将在每个月的开始日期和结束日期之间生成一组记录，而不会在任何地方创建实际记录。这是一个方便的技巧!

注意，你需要为你的CalendarCTE设置一个日期范围:你需要一个固定的开始日期，在你的CTE的第一个记录，如果你不设置结束日期，它将无限期地继续!