';' 구분 기호를 사용하여 행에서 열로의 Mysql

';' 구분 기호를 사용하여 행에서 열로의 Mysql

Mariadb 10.1에 테이블(카탈로그)이 있습니다.

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

아래 표와 같이 카탈로그 표의 값을 계산하고 그룹화하려면 어떻게 해야 합니까?

 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

링크 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

최대 100개의 분할 구분 값을 처리할 수 있는 단순한 SQL 방법(필요에 따라 쉽게 확장할 수 있음):-

SELECT result, COUNT(id)
FROM
(
    SELECT id, SUBSTRING_INDEX(SUBSTRING_INDEX(value, ' ; ', tens.anum * 10 + units.anum + 1), ' ; ', -1) AS result
    FROM Catalogs
    CROSS JOIN
    (SELECT 1 AS anum UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) units
    CROSS JOIN
    (SELECT 1 AS anum UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) tens
    WHERE LENGTH(value) - LENGTH(REPLACE(value, ';', '')) >= ( tens.anum * 10 + units.anum)
) sub0
GROUP BY result

언급URL : https://stackoverflow.com/questions/35148425/mysql-row-to-column-with-separator