生命周期销售总额在更新订单状态时不会发生变化

-- 创建订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    status VARCHAR(20),
    amount DECIMAL(10, 2)
);

-- 创建产品表，包含生命周期销售总额字段
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    total_sales DECIMAL(10, 2)
);

-- 更新订单状态的存储过程，同时更新产品的销售总额
DELIMITER //
CREATE PROCEDURE UpdateOrderStatus(IN p_order_id INT, IN p_new_status VARCHAR(20))
BEGIN
    DECLARE v_amount DECIMAL(10, 2);
    
    -- 获取订单金额
    SELECT amount INTO v_amount FROM orders WHERE order_id = p_order_id;
    
    -- 更新订单状态
    UPDATE orders SET status = p_new_status WHERE order_id = p_order_id;
    
    -- 根据订单状态调整产品销售总额
    IF p_new_status = '已支付' THEN
        UPDATE products SET total_sales = total_sales + v_amount WHERE product_id = (SELECT product_id FROM orders WHERE order_id = p_order_id);
    ELSEIF p_new_status = '已取消' THEN
        UPDATE products SET total_sales = total_sales - v_amount WHERE product_id = (SELECT product_id FROM orders WHERE order_id = p_order_id);
    END IF;
END //
DELIMITER ;