Mysql 行到列，分隔符为 ';'

Question

我的 Mariadb 10.1 上有一个表(目录)

id   value
1    one ; two ; one
2    two ; three ; one
3    four ; five
4    one
5    four ; one

我如何对 Catalogs 表中的值进行计数和分组，如下表所示。

 result    count
    one      5
    two      2
    three    1
    four     2
    five     1

或这张表

id value
1    one
1    two
1    one
2    two
2    three
2    one
3    four
3    five
4    one
5    four
5    one

Answer 1

引用链接http://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/

假设您有一个名为 table1 的表，其中包含两列 id 和 value 并且 value 列包含逗号分离值。

修改后的程序:

CREATE  PROCEDURE `explode_table`(bound VARCHAR(255))
BEGIN

DECLARE id INT DEFAULT 0;
DECLARE value TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value varchar(25);
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
                                     FROM table1
                                     WHERE table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`id` INT NOT NULL,
`value` VARCHAR(56) NOT NULL
) engine=memory;

OPEN cur1;
  read_loop: LOOP
    FETCH cur1 INTO id, value;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET occurance = (SELECT LENGTH(value)
                             - LENGTH(REPLACE(value, bound, ''))
                             +1);
    SET i=1;
    WHILE i <= occurance DO
      SET splitted_value =
      trim((SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
      LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ';', '')));

      INSERT INTO table2 VALUES (id, splitted_value);
      SET i = i + 1;

    END WHILE;
  END LOOP;

 CLOSE cur1;

 SELECT * FROM table2;
 END