mysql - mysql 游标中使用 Group_Concat 返回错误结果

Question

以下代码应该为 csv2 生成 3 个条目...但是生成了 6 个条目。

这是为什么呢？代码正在编写中。

创建代码:

Create table testing(
  idString varchar(20),
  region varchar(20)
);

insert into testing values("abcdefg","123456");
insert into testing values("bcdefgh","123456");
insert into testing values("cdefghi","123456");
insert into testing values("defghij","456789");
insert into testing values("efghijk","456789");
insert into testing values("fghijkl","456789");

要运行的代码:

DROP PROCEDURE IF EXISTS `sp_split`;
DROP PROCEDURE IF EXISTS `getCombinations`;

CREATE TABLE IF NOT EXISTS `temp` (
  `col` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS `result` (
  `col1` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;


CREATE PROCEDURE getCombinations()
  BEGIN

    DECLARE countInt INT DEFAULT 0;
    DECLARE csv varchar(100);
    DECLARE region varchar(100);
    DECLARE v_last_row_fetched INT;
    declare v_counter int unsigned default 1;

    DECLARE counter cursor for
      select  * from ( select region as region,
                              count(distinct idString) as countInt,
                              group_concat(idString) as csv
                          from testing
                          group by region ) temp;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_row_fetched=1;
    SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

    delete from result;
    open counter;
    c1_loop: loop
      fetch counter into region , countInt, csv ;


      IF  v_last_row_fetched=1 THEN LEAVE c1_loop; END IF;

      #DEBUG STATEMENT HERE - 6 values instead of two displayed for csv3
      select csv as csv3;
      LEAVE c1_loop;

    end loop c1_loop;
    close counter;

  END;

DELIMITER ;

CALL getCombinations();
SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

Answer 1

问题是您有一个名为 region 的局部变量，并且该变量在 GROUP BY Region 而不是列中使用。因此，就好像您编写了 GROUP BY null，因此所有内容都分组在一起。

更改region变量的名称，它将起作用。一般来说，避免使用与表列同名的变量，这通常会导致问题。