溫馨提示×

如何使用存儲(chǔ)過程在MySQL中實(shí)現(xiàn)數(shù)據(jù)透視

小樊
83
2024-09-12 02:50:54
欄目: 云計(jì)算

在MySQL中，您可以使用存儲(chǔ)過程來實(shí)現(xiàn)數(shù)據(jù)透視

  1. 首先，創(chuàng)建一個(gè)包含數(shù)據(jù)的示例表：
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);
  1. 創(chuàng)建一個(gè)存儲(chǔ)過程來實(shí)現(xiàn)數(shù)據(jù)透視：
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 ;
  1. 調(diào)用存儲(chǔ)過程以查看透視后的數(shù)據(jù)：
CALL pivot_sales_data();

這將返回以下結(jié)果：

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

請注意，此示例僅適用于具有固定月份列的簡單數(shù)據(jù)透視。對于更復(fù)雜的數(shù)據(jù)透視需求，您可能需要根據(jù)您的具體情況修改存儲(chǔ)過程。

0 踩