Mysql:将查询结果存储到单独的临时/虚拟表中

Question

我想要的:我想在单独的查询中处理每一行结果。

我现在拥有的: 从 [QUERY] 中选择 field1、field2 INTO @field1、@field2。

问题:如果[QUERY]返回多个结果，我将收到异常。

问题:[main]:如何将结果存储到表中？

问题:[sub]:如何对结果表的每一行执行单独的查询？

即:

SET result_table = SELECT field1, field2 INTO @field1, @field2 FROM [QUERY]
For each row of result_table do QUERY2

Answer 1

您考虑过使用光标吗？

DELIMITER $$
CREATE PROCEDURE sample_procedure
BEGIN
    -- We need this variable to identify the end of our procedure
    DECLARE finished_flag INTEGER DEFAULT 0;
    -- These are variables for the fields you have, match the types.
    DECLARE field1 varchar(100);
    DECLARE field2 int;
    DECLARE field3 datetime;

    -- declare cursor for our select
    DECLARE run_foreach_cursor CURSOR FOR 
    SELECT
        field1,
        field2
    FROM [QUERY];

    -- declare NOT FOUND so we know when we've run out of results
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished_flag = 1;

    -- Open the cursor
    OPEN run_foreach_cursor;

    run_foreach_content: LOOP

        -- Put fields list on this line, this is where they go.
        FETCH run_foreach_cursor INTO field1, field2;

        IF finished_flag = 1 THEN 
            LEAVE run_foreach_content;
        END IF;

        -- Do your query here.
        INSERT INTO [tbl2]
        VALUES
           (
              field1,
              field2 + 7
           );

    -- Marks the end of our loop
    END LOOP run_foreach_content;

    -- Close the cursor so MySQL can free up the query attached to it. 
    CLOSE run_foreach_cursor;

END $$

DELIMITER ;

CALL sample_procedure();