MySql 查询 - 将数据扩展到新表中

Question

我将数据存储在 MySQL 表中，需要提取它并以非常具体的方式将其存储到新表中:

数据位于客户表中，例如:

ID String | Region Identifier
abcdefg   | 123456
bcdefgh   | 123456
cdefghi   | 123456
defghij   | 456789
efghijk   | 456789
fghijkl   | 456789

我希望能够显示某个区域标识符下的所有 ID 字符串组合(该区域中有多个 ID 字符串)并将其存储在另一个表中。例如，上述数据将最终出现在另一个表中，例如:

ID Strings                 | Region Identifier | Count
abcdefg, bcdefgh, cdefghi  | 123456            |  3
abcdefg, cdefghi           | 123456            |  2
abcdefg, bcdefgh           | 123456            |  2
bcdefgh, cdefghi           | 123456            |  2
defghij, efghijk, fghijkl  | 456789            |  3
defghij, fghijkl           | 456789            |  2
defghij, efghijk           | 456789            |  2
efghijk, fghijkl           | 456789            |  2

我还想确保 ID 字符串(可以是数字和字母的组合)按字母顺序显示(在 ID 字符串字段内)，并且需要根据字符串将它们转换为整数。例如(abcdefg、bcdefgh、cdefghi)将变为 2454574579。(只要这 3 个确切的字符串组合在一起，我就需要能够获取此 ID)。

如果可能的话，我更愿意使用原始 SQL，但如果不是的话，我很乐意使用 python、php、javascript，但只要是最快的。

Answer 1

问题是您需要组合的总数，该总数可能是无限的，具体取决于每个区域的唯一 ID 的数量。如果您有 X 个唯一 ID，则需要进行 X 次自连接。每个不带 where 条件的连接都可以为您生成另一个组合。

所以你需要使用存储过程。

第一步是进行 group_concat。我还将使用以下存储过程:https://gist.github.com/avoidwork/3749973

您使用组连接:

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");


select region, group_concat(idString) from testing group by region;

剩下的，我将在稍后发布，是使用 ascii 值转换为字符的数字等效值。并且还可以进行订购。

更新

根据需要更改代码。请注意，下面获取 IDString 所有顺序的所有组合。

        #Init Stuff

#Will not work without the following command/Setting:
SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');

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` (
  `idStrings` VARCHAR(100) NOT NULL,
  `region` VARCHAR(100) NOT NULL,
  `count` VARCHAR(100) NOT NULL
) ENGINE=MEMORY;

delete from result;
delete from temp;

create view  IF NOT EXISTS groupByBugWorkaroundView as select   region as region, count(distinct idString) as countInt, group_concat(idString) as csv from testing group by region;

delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_split`(IN toSplit text, IN target char(255))
  BEGIN
    # Temp table variables
    SET @tableName = 'tmpSplit';
    SET @fieldName = 'variable';

    #select toSplit;

    # Dropping table
    SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Creating table
    SET @sql := CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;


    # Preparing toSplit
    SET @vars := toSplit;
    SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");

    delete from tmpSplit;

    # Inserting values
    SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Returning record set, or inserting into optional target
    IF target IS NULL THEN
      SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
    ELSE
      SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END
;;

#-------------------------------------------------------------------------------

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 groupByBugWorkaroundView;

    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;
    delete from tmpSplit;
    COMMIT;

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

      IF  v_last_row_fetched=1 THEN LEAVE c1_loop; END IF;

      CALL sp_split(csv, null);

      #       delete from temp;
      #       insert into temp select * from tempSplit;
      #       commit;

      set @q0 = concat('insert into result select t0.variable ');
      set @q1 = concat(' from tmpSplit t0');
      set @q3 = concat(' where 1=1 ');
      set @q4 = ',\''  || region || ' \'' ;

      #-- DEBUG
      #       set @tempLog = concat('insert into templog values (\'',@q1);
      #       set @tempLog = concat(@q1, ' '');');
      #       PREPARE stmt1 FROM @tempLog;
      #       EXECUTE stmt1;
      #       DEALLOCATE PREPARE stmt1;
      #-- END DEBUG

      set v_counter = 1;
      while v_counter < countInt do

        set @q1 := concat(@q1,  ' ,tmpSplit t');
        set @q1 := concat(@q1 ,  v_counter);

        set @q0 := concat(@q0, '|| \',\' || t');
        set @q0 := concat(@q0, v_counter);
        set @q0 := concat(@q0, '.variable');

        #Necessary joins
        set @q3 := concat(@q3, ' AND not t');
        set @q3 := concat(@q3, v_counter-1);
        set @q3 := concat(@q3, '.variable = ');
        set @q3 := concat(@q3, ' t');
        set @q3 := concat(@q3, v_counter);
        set @q3 := concat(@q3, '.variable ');

        #Alphabetical order
        set @q3 := concat(@q3, ' AND not t');
        set @q3 := concat(@q3, v_counter-1);
        set @q3 := concat(@q3, '.variable > ');
        set @q3 := concat(@q3, ' t');
        set @q3 := concat(@q3, v_counter);
        set @q3 := concat(@q3, '.variable ');

        set v_counter=v_counter+1;

        set @q5 = ',\''  || v_counter || '\'' ;

        set @q9 := concat('', '');
        set @q9 := concat( @q0, @q4);
        set @q9 := concat( @q9, @q5);
        set @q9 := concat( @q9, @q1);
        set @q9 := concat( @q9, @q3);
        select @q9 as debug;

        PREPARE stmt1 FROM @q9;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        commit;

      end while;

      delete from temp; commit;

    end loop c1_loop;
    close counter;

  END;

DELIMITER ;

CALL getCombinations();
select distinct idStrings, region, count from result order by region, count  desc;

数据库 fiddle

https://www.db-fiddle.com/f/mHyWsoRjoQwESHtuLiFQ2y/0

优化更新

此版本将多个插入语句替换为仅一个插入语句:

https://www.db-fiddle.com/f/mHyWsoRjoQwESHtuLiFQ2y/0

#Will not work without the following command/Setting:
SET SESSION sql_mode = CONCAT(@@sql_mode, ',PIPES_AS_CONCAT');


create table IF NOT EXISTS testing(
  idString varchar(20),
  region varchar(20)
);

delete from testing;

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 `tmpSplit` (
  `variable` VARCHAR(100) NOT NULL
)
  ENGINE = MEMORY;

CREATE TABLE IF NOT EXISTS `result` (
  `idStrings` VARCHAR(100) NOT NULL,
  `region`    VARCHAR(100) NOT NULL,
  `count`     VARCHAR(100) NOT NULL
)
  ENGINE = MEMORY;

DELETE FROM result;
DELETE FROM temp;


DROP VIEW IF EXISTS `testingWithBlanks`;
CREATE VIEW testingWithBlanks AS
  SELECT
    idString,
    region
  FROM testing
  UNION
  select  distinct ' ' as idString , region from testing;

DROP VIEW IF EXISTS `groupByBugWorkaroundView`;
CREATE VIEW groupByBugWorkaroundView AS
  SELECT
    region                   AS region,
    count(DISTINCT idString) AS countInt,
    group_concat(idString)   AS csv
  FROM testingWithBlanks
  GROUP BY region;

CREATE PROCEDURE `sp_split`(IN toSplit TEXT, IN target CHAR(255))
  BEGIN
    # Temp table variables
    SET @tableName = 'tmpSplit';
    SET @fieldName = 'variable';

    #select toSplit;

    # Dropping table
    SET @sql := CONCAT('DROP TABLE IF EXISTS ', @tableName);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Creating table
    SET @sql := CONCAT('CREATE TABLE IF NOT EXISTS ', @tableName, ' (', @fieldName, ' VARCHAR(1000))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Preparing toSplit
    SET @vars := toSplit;
    SET @vars := CONCAT("('", REPLACE(@vars, ",", "'),('"), "')");

    DELETE FROM tmpSplit;

    # Inserting values
    SET @sql := CONCAT('INSERT INTO ', @tableName, ' VALUES ', @vars);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    # Returning record set, or inserting into optional target
    IF target IS NULL
    THEN
      SET @sql := CONCAT('SELECT TRIM(`', @fieldName, '`) AS `', @fieldName, '` FROM ', @tableName);
    ELSE
      SET @sql := CONCAT('INSERT INTO ', target, ' SELECT TRIM(`', @fieldName, '`) FROM ', @tableName);
    END IF;

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

  END;


DROP FUNCTION IF EXISTS `subStringCount`;
CREATE FUNCTION `subStringCount`(sequence VARCHAR(1000), word VARCHAR(100))
  RETURNS INT(4)
DETERMINISTIC
CONTAINS SQL
  BEGIN
    DECLARE counter SMALLINT UNSIGNED DEFAULT 0;
    DECLARE word_length SMALLINT UNSIGNED;

    SET word_length = CHAR_LENGTH(word);

    WHILE (INSTR(sequence, word) != 0) DO
      SET counter = counter + 1;
      SET sequence = SUBSTR(sequence, INSTR(sequence, word) + word_length);
    END WHILE;

    RETURN counter;
  END;


CREATE PROCEDURE getCombinations()
  BEGIN

    DECLARE countInt INT DEFAULT 0;
    DECLARE csv VARCHAR(100);
    DECLARE region2 VARCHAR(100);
    DECLARE v_last_row_fetched INT;
    DECLARE v_counter INT UNSIGNED DEFAULT 1;

    DECLARE counter CURSOR FOR SELECT *
                               FROM groupByBugWorkaroundView;

    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;
    DELETE FROM tmpSplit;
    COMMIT;

    OPEN counter;
    c1_loop: LOOP
      FETCH counter
      INTO region2, countInt, csv;

      IF v_last_row_fetched = 1
      THEN LEAVE c1_loop;
      END IF;

      CALL sp_split(csv, NULL);

      #       delete from temp;
      #       insert into temp select * from tempSplit;
      #       commit;

      SET @q0 = concat('insert into result select t0.variable ');
      SET @q1 = concat(' from tmpSplit t0');
      SET @q3 = concat(' where 1=1 ');
      SET @q4 = ',\'' || region2 || ' \'';

      #-- DEBUG
      #       set @tempLog = concat('insert into templog values (\'',@q1);
      #       set @tempLog = concat(@q1, ' '');');
      #       PREPARE stmt1 FROM @tempLog;
      #       EXECUTE stmt1;
      #       DEALLOCATE PREPARE stmt1;
      #-- END DEBUG

      SET v_counter = 1;
      WHILE v_counter < countInt DO

        SET @q1 := concat(@q1, ' ,tmpSplit t');
        SET @q1 := concat(@q1, v_counter);

        SET @q0 := concat(@q0, '|| \',\' || t');
        SET @q0 := concat(@q0, v_counter);
        SET @q0 := concat(@q0, '.variable');

        #Necessary joins
        SET @q3 := concat(@q3, ' AND (not t');
        SET @q3 := concat(@q3, v_counter - 1);
        SET @q3 := concat(@q3, '.variable = ');
        SET @q3 := concat(@q3, ' t');
        SET @q3 := concat(@q3, v_counter);
        SET @q3 := concat(@q3, '.variable ');

        SET @q3 := concat(@q3, ' OR t');
        SET @q3 := concat(@q3, v_counter - 1);
        SET @q3 := concat(@q3, '.variable = ');
        SET @q3 := concat(@q3, '\' \')  ');

        #Alphabetical order
        SET @q3 := concat(@q3, ' AND not t');
        SET @q3 := concat(@q3, v_counter - 1);
        SET @q3 := concat(@q3, '.variable > ');
        SET @q3 := concat(@q3, ' t');
        SET @q3 := concat(@q3, v_counter);
        SET @q3 := concat(@q3, '.variable ');

        SET v_counter = v_counter + 1;

        SET @q5 = ',\'' || v_counter || '\'';

        SET @q9 := concat('', '');
        SET @q9 := concat(@q0, @q4);
        SET @q9 := concat(@q9, @q5);
        SET @q9 := concat(@q9, @q1);
        SET @q9 := concat(@q9, @q3);


      END WHILE;

      SELECT @q9 AS debug;

      PREPARE stmt1 FROM @q9;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;
      COMMIT;

      DELETE FROM temp;
      COMMIT;

    END LOOP c1_loop;
    CLOSE counter;

  END;


CALL getCombinations();

SELECT
  idStrings,
  region,
  subStringCount(idStrings, ',') + 1 AS count
FROM
  (
    SELECT DISTINCT
      REPLACE(idStrings, ' ,', '') AS idStrings,
      region
    FROM result
  ) temp
WHERE subStringCount(idStrings, ',') > 0
ORDER BY region, subStringCount(idStrings, ',') + 1 DESC;

更新

关于人们可以改进哪些方面的评论(如待办事项):

https://www.db-fiddle.com/f/wJ2XuB893is9ENaXxuFgCZ/0