mysql用光标横纵表转换

基础概念

MySQL中的游标（Cursor）是一种数据库对象，用于处理查询结果集。游标允许程序逐行地访问查询结果集中的每一行数据，而不是一次性将所有数据加载到内存中。这在处理大量数据时非常有用，因为它可以提高性能并减少内存消耗。

横纵表转换通常指的是将数据从一种表结构转换为另一种表结构的过程。例如，将行数据转换为列数据，或者将列数据转换为行数据。

类型

MySQL中的游标主要有两种类型：

隐式游标：在执行SELECT语句时自动创建的游标，无需显式声明。
显式游标：需要显式声明和打开的游标，提供了更多的控制选项。

应用场景

游标常用于以下场景：

数据转换：将数据从一种表结构转换为另一种表结构。
逐行处理：需要对查询结果集中的每一行数据进行复杂处理的场景。
数据导入/导出：将数据从数据库导出到文件，或者从文件导入到数据库。

示例代码

以下是一个使用显式游标进行横纵表转换的示例：

-- 创建示例表
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();