如何使用存储过程在MySQL中实现数据透视

在MySQL中，您可以使用存储过程来实现数据透视

1. 首先，创建一个包含数据的示例表：

CREATE TABLE sales_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    month VARCHAR(255),
    sales INT
);

INSERT INTO sales_data (product_name, month, sales)
VALUES ('Product A', 'January', 100),
       ('Product A', 'February', 200),
       ('Product B', 'January', 150),
       ('Product B', 'February', 250);

2. 创建一个存储过程来实现数据透视：

DELIMITER //
CREATE PROCEDURE pivot_sales_data()
BEGIN
    -- Declare variables
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT DISTINCT month FROM sales_data;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    DECLARE @query VARCHAR(4096);
    DECLARE @month VARCHAR(255);

    -- Create a temporary table to store pivoted data
    DROP TEMPORARY TABLE IF EXISTS temp_pivot;
    CREATE TEMPORARY TABLE temp_pivot (
        product_name VARCHAR(255)
    );

    -- Open the cursor and loop through each month
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO @month;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Add a new column for each month in the temporary table
        SET @query = CONCAT('ALTER TABLE temp_pivot ADD COLUMN `', @month, '` INT');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;

    -- Populate the temporary table with pivoted data
    SET @query = '
        INSERT INTO temp_pivot (product_name, January, February)
        SELECT product_name,
               SUM(CASE WHEN month = ''January'' THEN sales ELSE 0 END) AS January,
               SUM(CASE WHEN month = ''February'' THEN sales ELSE 0 END) AS February
        FROM sales_data
        GROUP BY product_name';
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Select the pivoted data from the temporary table
    SELECT * FROM temp_pivot;
END//
DELIMITER ;

3. 调用存储过程以查看透视后的数据：

CALL pivot_sales_data();

这将返回以下结果：

+-------------+---------+----------+
| product_name | January | February |
+-------------+---------+----------+
| Product A   |     100 |      200 |
| Product B   |     150 |      250 |
+-------------+---------+----------+

请注意，此示例仅适用于具有固定月份列的简单数据透视。对于更复杂的数据透视需求，您可能需要根据您的具体情况修改存储过程。