MySQL 嵌套游标问题

Question

我有以下一堆代码尝试使用两个游标（一个在另一个游标内）来更新字段。我使用第一个光标只是为了获取 id 值（第一个光标），然后根据该 id（第二个光标）获取其他 id 值的列表。问题是第二个游标的结果集包含最后一个 id 两次！我找不到错误！循环中的某些内容，两个游标的终止变量中的某些内容？

非常欢迎任何帮助！提前致谢！

分隔符 $$ 如果存在则删除程序 updateNumberOfPoisForPlacesWithChildren$$ 创建过程 updateNumberOfPoisForPlacesWithChildren() 开始 声明 single_place_id INT(11); 声明 single_unique_parents_id INT(11); 声明完成，temp_number_of_pois，sum_number_of_pois INT 默认值 0；

    DECLARE unique_parents_ids CURSOR FOR SELECT DISTINCT `parent_id` FROM `places` WHERE `parent_id` IS NOT NULL;
    DECLARE temp_places_ids CURSOR FOR SELECT `id` FROM `places` WHERE `parent_id` = single_unique_parents_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN unique_parents_ids;

    main_loop_for_parent_ids : LOOP
        SET sum_number_of_pois = 0;
        IF done = 1 THEN
            CLOSE unique_parents_ids;
            LEAVE main_loop_for_parent_ids;
        END IF;

        IF NOT done = 1 THEN
            FETCH unique_parents_ids INTO single_unique_parents_id;
            OPEN temp_places_ids;
            main_loop_for_places : LOOP
                IF done = 1 THEN
                    CLOSE temp_places_ids;
                    LEAVE main_loop_for_places;
                END IF;

                IF NOT done = 1 THEN
                    FETCH temp_places_ids INTO single_place_id;
                    SELECT COUNT(`id`) INTO temp_number_of_pois FROM `pois` WHERE `place_id` = single_place_id;
                    SET sum_number_of_pois = sum_number_of_pois + temp_number_of_pois;
                END IF;

            END LOOP;   

            UPDATE `places` SET `number_of_pois`=sum_number_of_pois WHERE `id` = single_unique_parents_id;

        END IF;
    END LOOP;



END$$

分隔符；

Answer 1

我认为你的问题就在这里......

        IF NOT done = 1 THEN
            FETCH temp_places_ids INTO single_place_id;
            SELECT COUNT(`id`) INTO temp_number_of_pois FROM `pois` WHERE `place_id` = single_place_id;
            SET sum_number_of_pois = sum_number_of_pois + temp_number_of_pois;
        END IF;

FETCH 在耗尽结果集时将 DONE 设置为 1，但你继续，因为你已经通过了 DONE 测试。

也许...

 FETCH temp_places_ids INTO single_place_id;
 IF NOT done = 1 THEN
     SELECT COUNT(`id`) INTO temp_number_of_pois FROM `pois` WHERE `place_id` = single_place_id;
     SET sum_number_of_pois = sum_number_of_pois + temp_number_of_pois;
 END IF;