mysql 聚合表

-- 创建基础销售表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

-- 插入一些示例数据
INSERT INTO sales (sale_id, product_id, sale_date, amount) VALUES
(1, 101, '2023-01-01', 100.00),
(2, 102, '2023-01-02', 200.00),
...;

-- 创建汇总表来存储每月的总销售额
CREATE TABLE monthly_sales_summary (
    month DATE,
    total_sales DECIMAL(10, 2),
    PRIMARY KEY (month)
);

-- 使用触发器或定期任务来更新汇总表
DELIMITER //
CREATE TRIGGER update_monthly_sales
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    DECLARE month_start DATE;
    SET month_start = DATE_FORMAT(NEW.sale_date, '%Y-%m-01');
    
    -- 更新或插入汇总数据
    INSERT INTO monthly_sales_summary (month, total_sales)
    VALUES (month_start, (SELECT IFNULL(SUM(amount), 0) FROM sales WHERE DATE_FORMAT(sale_date, '%Y-%m') = DATE_FORMAT(month_start, '%Y-%m')))
    ON DUPLICATE KEY UPDATE total_sales = VALUES(total_sales);
END//
DELIMITER ;