存储过程按日期范围及订单类型统计PaymentAmount求和异常排查

原代码存在的核心问题 #

1. 重复行成因：CTE_Revenue按Type和OrderDateTime分组，导致每日每个订单类型生成独立行，关联日期序列后自然出现重复记录
2. 关联逻辑错误：使用FORMAT转换字符串匹配日期，容易因格式不一致导致关联失败，且字符串匹配效率远低于日期类型直接匹配
3. WHERE子句逻辑错误：Type = 'Delivery' OR Type ='Online' AND [P].[DeletedFlag] = 0中，AND优先级高于OR，实际逻辑不符合预期；同时引用了未定义的表别名P
4. NULL值与列结构问题：未将不同类型的统计结果转为独立列，也未处理无数据日期的NULL值，不符合期望的$0显示要求

修正后的存储过程代码 #

CREATE PROCEDURE GetDailyRevenue
    @StartDate DATE = '2021-02-01',
    @EndDate DATE = '2021-02-28' -- 注意：2021年2月无30日，若需保留原输入可自行处理日期转换
AS
BEGIN
    SET NOCOUNT ON;

    -- 生成日期范围的每日序列
    ;WITH Dates(RevenueDate) AS (
        SELECT @StartDate AS RevenueDate
        UNION ALL
        SELECT DATEADD(DAY, 1, RevenueDate)
        FROM Dates
        WHERE DATEADD(DAY, 1, RevenueDate) <= @EndDate
    ),
    -- 按日期+类型聚合收入
    DailyTypeRevenue AS (
        SELECT
            CAST(OrderDateTime AS DATE) AS OrderDate,
            Type,
            SUM(PaymentAmount) AS TotalAmount
        FROM OrderInfo
        WHERE
            (Type = 'Delivery' OR Type = 'Online')
            AND DeletedFlag = 0 -- 移除未定义的表别名P
        GROUP BY
            CAST(OrderDateTime AS DATE),
            Type
    ),
    -- 将类型转为独立列（条件聚合）
    PivotedRevenue AS (
        SELECT
            OrderDate,
            SUM(CASE WHEN Type = 'Delivery' THEN TotalAmount ELSE 0 END) AS Delivery,
            SUM(CASE WHEN Type = 'Online' THEN TotalAmount ELSE 0 END) AS Online
        FROM DailyTypeRevenue
        GROUP BY OrderDate
    )
    -- 关联日期序列与聚合结果，处理NULL并格式化货币
    SELECT
        d.RevenueDate,
        FORMAT(ISNULL(p.Delivery, 0), 'C', 'en-US') AS Delivery,
        FORMAT(ISNULL(p.Online, 0), 'C', 'en-US') AS Online
    FROM Dates d
    LEFT JOIN PivotedRevenue p ON d.RevenueDate = p.OrderDate
    ORDER BY d.RevenueDate
    OPTION (MAXRECURSION 0);
END

关键修正说明 #

1. 日期序列优化：直接使用DATE类型生成序列，避免不必要的类型转换，提升效率
2. 聚合逻辑修正：按日期（转换为DATE类型）和类型分组，确保每日每个类型仅一条统计记录；修正WHERE子句的逻辑优先级，明确筛选条件
3. 列转换处理：使用条件聚合将Delivery和Online转为独立列，从根源避免重复行
4. NULL值与格式化：用ISNULL将无数据的统计值转为0，再通过FORMAT函数格式化为货币格式（如$500）
5. 存储过程封装：参数化日期范围，增强代码复用性

补充说明 #

2021年2月不存在30日，SQL Server会自动将'2021-02-30'转换为'2021-03-02'。若需严格显示“2021-02-30”（尽管是无效日期），需将日期作为字符串处理，但不推荐使用无效日期，建议改为有效日期如2021-02-28。

内容的提问来源于stack exchange，提问作者Sravan Dudyalu