mysql交叉表查询

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    region VARCHAR(50),
    amount DECIMAL(10, 2)
);

SELECT 
    product,
    SUM(CASE WHEN region = 'North' THEN amount ELSE 0 END) AS North,
    SUM(CASE WHEN region = 'South' THEN amount ELSE 0 END) AS South,
    SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS East,
    SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS West
FROM 
    sales
GROUP BY 
    product;

CREATE INDEX idx_product_region ON sales(product, region);

DELIMITER //

CREATE PROCEDURE generate_cross_tab()
BEGIN
    DECLARE regions VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_region VARCHAR(50);
    DECLARE sql_query VARCHAR(1000);

    -- 获取所有地区
    SET regions = (SELECT GROUP_CONCAT(DISTINCT region) FROM sales);

    -- 构建动态SQL查询
    SET sql_query = 'SELECT product, ';
    SET @i = 0;
    WHILE NOT done DO
        SET @i = @i + 1;
        SET cur_region = SUBSTRING_INDEX(SUBSTRING_INDEX(regions, ',', @i), ',', -1);
        IF cur_region IS NULL THEN
            SET done = TRUE;
        ELSE
            SET sql_query = CONCAT(sql_query, 'SUM(CASE WHEN region = ''', cur_region, ''' THEN amount ELSE 0 END) AS ', cur_region, ', ');
        END IF;
    END WHILE;
    SET sql_query = SUBSTRING(sql_query, 1, LENGTH(sql_query) - 2); -- 去掉最后一个逗号和空格
    SET sql_query = CONCAT(sql_query, ' FROM sales GROUP BY product');

    -- 执行动态SQL查询
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;