mysql用光标横纵表转换

-- 创建示例表
CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    sales_amount INT,
    sale_date DATE
);

-- 插入示例数据
INSERT INTO sales (id, product, sales_amount, sale_date)
VALUES (1, 'Product A', 100, '2023-01-01'),
       (2, 'Product B', 200, '2023-01-02'),
       (3, 'Product A', 150, '2023-01-03');

-- 使用游标进行横纵表转换
DELIMITER //

CREATE PROCEDURE transform_sales()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE product VARCHAR(50);
    DECLARE sales_amount INT;
    DECLARE sale_date DATE;
    DECLARE cur CURSOR FOR SELECT product, sales_amount, sale_date FROM sales;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储转换后的数据
    DROP TEMPORARY TABLE IF EXISTS sales_summary;
    CREATE TEMPORARY TABLE sales_summary (
        product VARCHAR(50),
        total_sales_amount INT,
        sale_dates TEXT
    );

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO product, sales_amount, sale_date;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 更新临时表中的数据
        INSERT INTO sales_summary (product, total_sales_amount, sale_dates)
        VALUES (product, sales_amount, CONCAT(sale_date, ',')) ON DUPLICATE KEY UPDATE
        total_sales_amount = total_sales_amount + sales_amount,
        sale_dates = CONCAT(sales_summary.sale_dates, sale_date, ',');
    END LOOP;

    CLOSE cur;

    -- 查询转换后的数据
    SELECT * FROM sales_summary;
END //

DELIMITER ;

-- 调用存储过程
CALL transform_sales();