mysql数据及时统计

-- 创建一个示例表
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    sale_date DATETIME
);

-- 创建一个统计表
CREATE TABLE sales_stats (
    product_id INT PRIMARY KEY,
    total_quantity INT,
    last_updated DATETIME
);

-- 创建触发器，在销售记录插入时更新统计数据
DELIMITER $$
CREATE TRIGGER update_sales_stats
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE sales_stats
    SET total_quantity = total_quantity + NEW.quantity,
        last_updated = NOW()
    WHERE product_id = NEW.product_id;

    IF ROW_COUNT() = 0 THEN
        INSERT INTO sales_stats (product_id, total_quantity, last_updated)
        VALUES (NEW.product_id, NEW.quantity, NOW());
    END IF;
END$$
DELIMITER ;