如何在SQL Server中合并日期序列与洗车统计查询以显示全日期数据

原查询用于统计洗车行每日洗车次数，但闭店日期（如2023-01-22）会因无交易而缺失，需要让这些日期显示为0次。原查询代码如下：

SELECT
    count(st.date) as NumberOfWashes,
    cast(st.date as date) as DayOfWashes
FROM 
    POS.dbo.sales_transaction_line_item as stli
    join POS.dbo.sales_transaction as st on st.sales_transaction_id = stli.fk_sales_transaction
    join POS.dbo.sales_item as si on si.sales_item_id = stli.fk_sales_item
WHERE
    st.fk_sales_status <> 3
    and si.fk_sales_item_type = 1
    and st.date BETWEEN @start_date and @end_date
Group by
    cast(st.date as date)
order by
    cast(st.date as date) desc

原查询结果缺失无交易日期：

NumberofWashes DayOfWashes
42             2023-01-26
71             2023-01-25
57             2023-01-24
87             2023-01-23
104            2023-01-21
114            2023-01-20

已生成全日期序列的CTE代码：

DECLARE @start_date DATE = '2021-01-26';
DECLARE @end_date DATE = '2023-01-27';

WITH AllDays
AS ( SELECT   @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date )
SELECT [Date]
FROM   AllDays 
OPTION (MAXRECURSION 0)

合并后的完整SQL #

将全日期CTE作为主表，用LEFT JOIN关联原查询的统计结果，并用*ISNULL()*函数将无交易日期的次数转为0：

DECLARE @start_date DATE = '2021-01-26';
DECLARE @end_date DATE = '2023-01-27';

WITH AllDays
AS ( 
    SELECT @start_date AS [Date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date])
    FROM AllDays
    WHERE [Date] < @end_date 
),
WashStats AS (
    SELECT
        COUNT(st.date) AS NumberOfWashes,
        CAST(st.date AS DATE) AS DayOfWashes
    FROM 
        POS.dbo.sales_transaction_line_item AS stli
        JOIN POS.dbo.sales_transaction AS st ON st.sales_transaction_id = stli.fk_sales_transaction
        JOIN POS.dbo.sales_item AS si ON si.sales_item_id = stli.fk_sales_item
    WHERE
        st.fk_sales_status <> 3
        AND si.fk_sales_item_type = 1
        AND st.date BETWEEN @start_date AND @end_date
    GROUP BY
        CAST(st.date AS DATE)
)
SELECT
    ISNULL(ws.NumberOfWashes, 0) AS NumberOfWashes,
    ad.[Date] AS DayOfWashes
FROM AllDays ad
LEFT JOIN WashStats ws ON ad.[Date] = ws.DayOfWashes
ORDER BY ad.[Date] DESC
OPTION (MAXRECURSION 0)

关键逻辑说明 #

1. 将原统计查询封装为名为WashStats的CTE，保留原有过滤和分组逻辑
2. 用LEFT JOIN让全日期序列（AllDays）左关联统计结果，确保所有日期都被保留
3. 使用*ISNULL(ws.NumberOfWashes, 0)*将无匹配的日期次数替换为0
4. 最终按日期倒序排序，得到包含所有日期的统计结果

内容的提问来源于stack exchange，提问作者Georg Huber