mysql 横表转竖表

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    region VARCHAR(50),
    sales_amount INT
);

INSERT INTO sales (product, region, sales_amount) VALUES
('ProductA', 'Region1', 100),
('ProductA', 'Region2', 150),
('ProductB', 'Region1', 200),
('ProductB', 'Region2', 250);

SELECT 
    product,
    SUM(CASE WHEN region = 'Region1' THEN sales_amount ELSE 0 END) AS Region1,
    SUM(CASE WHEN region = 'Region2' THEN sales_amount ELSE 0 END) AS Region2
FROM 
    sales
GROUP BY 
    product;

CREATE TEMPORARY TABLE temp_sales AS
SELECT 
    product,
    SUM(CASE WHEN region = 'Region1' THEN sales_amount ELSE 0 END) AS Region1,
    SUM(CASE WHEN region = 'Region2' THEN sales_amount ELSE 0 END) AS Region2
FROM 
    sales
GROUP BY 
    product;

SELECT * FROM temp_sales;

-- 定期更新临时表
DELIMITER $$
CREATE EVENT IF NOT EXISTS update_temp_sales
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DROP TEMPORARY TABLE IF EXISTS temp_sales;
    CREATE TEMPORARY TABLE temp_sales AS
    SELECT 
        product,
        SUM(CASE WHEN region = 'Region1' THEN sales_amount ELSE 0 END) AS Region1,
        SUM(CASE WHEN region = 'Region2' THEN sales_amount ELSE 0 END) AS Region2
    FROM 
        sales
    GROUP BY 
        product;
END$$
DELIMITER ;