存储过程计算P1产品2020年销量错误，求助排查修正

问题根源 #

存储过程中使用date >= DATE_SUB(CURDATE(), interval 3 year)作为过滤条件，该条件仅保留当前日期往前推3年至今的订单。例如，若当前日期是2023年6月1日，三年前的日期是2020年6月1日，那么2020年1-5月的订单会被完全排除，导致SUM(CASE WHEN YEAR(date)=2020 THEN quantity ELSE 0 END)计算结果为0；而单独查询时直接匹配2020年全年数据，因此能得到正确的35。

解决方案 #

根据实际需求选择以下两种修正方式：

方式1：固定统计2020、2021、2022三年销量

如果需求是明确统计这三个固定年份的销量，直接修改WHERE条件为匹配指定年份，移除基于当前日期的动态过滤：

DELIMITER //
CREATE PROCEDURE evaluateProduct(
    IN input_productid VARCHAR(10),
    OUT total_sold_2020 INT,
    OUT total_sold_2021 INT,
    OUT total_sold_2022 INT,
    OUT total_sold_overall INT
)
BEGIN
    SELECT 
        SUM(CASE WHEN YEAR(date) = 2020 THEN quantity ELSE 0 END),
        SUM(CASE WHEN YEAR(date) = 2021 THEN quantity ELSE 0 END),
        SUM(CASE WHEN YEAR(date) = 2022 THEN quantity ELSE 0 END),
        SUM(quantity)
    INTO total_sold_2020, total_sold_2021, total_sold_2022, total_sold_overall
    FROM orders
    WHERE productID = input_productid
    AND YEAR(date) IN (2020, 2021, 2022);
END //

DELIMITER ;

方式2：动态统计最近三年销量（适配当前年份）

如果需求是统计“当前年份往前推3年”的销量（例如2024年时统计2021、2022、2023年），则调整CASE语句中的年份为动态计算，保留日期过滤条件：

DELIMITER //
CREATE PROCEDURE evaluateProduct(
    IN input_productid VARCHAR(10),
    OUT total_sold_prev3 INT,
    OUT total_sold_prev2 INT,
    OUT total_sold_prev1 INT,
    OUT total_sold_overall INT
)
BEGIN
    DECLARE current_year INT DEFAULT YEAR(CURDATE());
    SELECT 
        SUM(CASE WHEN YEAR(date) = current_year - 3 THEN quantity ELSE 0 END),
        SUM(CASE WHEN YEAR(date) = current_year - 2 THEN quantity ELSE 0 END),
        SUM(CASE WHEN YEAR(date) = current_year - 1 THEN quantity ELSE 0 END),
        SUM(quantity)
    INTO total_sold_prev3, total_sold_prev2, total_sold_prev1, total_sold_overall
    FROM orders
    WHERE productID = input_productid
    AND date >= DATE_SUB(CURDATE(), interval 3 year);
END //

DELIMITER ;

验证调用 #

修改后执行以下语句即可获取正确结果：

CALL evaluateProduct('p1', @total_sold_2020, @total_sold_2021, @total_sold_2022, @total_sold_overall);
SELECT @total_sold_2020, @total_sold_2021, @total_sold_2022, @total_sold_overall;

内容的提问来源于stack exchange，提问作者Walead Afshal