wake-up-neo.com

Was ist das Gegenteil von GROUP_CONCAT in MySQL?

Ich stoße anscheinend häufig auf dieses Problem, da ich Daten habe, die wie folgt formatiert sind:

+----+----------------------+
| id | colors               |
+----+----------------------+
| 1  | Red,Green,Blue       |
| 2  | Orangered,Periwinkle |
+----+----------------------+

aber ich möchte es so formatiert haben:

+----+------------+
| id | colors     |
+----+------------+
| 1  | Red        |
| 1  | Green      |
| 1  | Blue       |
| 2  | Orangered  |
| 2  | Periwinkle |
+----+------------+

Gibt es eine gute Möglichkeit, dies zu tun? Wie heißt diese Operation überhaupt?

mysqlcsvformatpivotgroup-concat
24
Jason Hamje

Ich denke, es ist das, was Sie brauchen (gespeicherte Prozedur): Mysql Spaltenzeichenfolge in Zeilen aufteilen

DELIMITER $$

DROP PROCEDURE IF EXISTS explode_table $$
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 INT;
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(255) 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 =
      (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;

  SELECT * FROM table2;
 CLOSE cur1;
 END; $$
9
kmas

Sie könnten eine Abfrage wie folgt verwenden:

SELECT
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
  colors
  INNER JOIN
  (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
  ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY
  id,
  n.digit

Bitte siehe Geige hier . Beachten Sie, dass diese Abfrage bis zu 4 Farben für jede Zeile unterstützt. Sie sollten Ihre Unterabfrage aktualisieren, um mehr als 4 Zahlen zurückzugeben (oder eine Tabelle mit 10 oder 100 Zahlen verwenden).

16
fthiella

wenn Trennzeichen Teil von Daten sind, aber in Anführungszeichen eingebettet sind, wie können wir sie dann aufteilen.

Beispiel Erste, "zweite, s", dritte

es sollte als erster zweiter, s dritter kommen

0
sailesh

Das hat mir viele Stunden erspart! Noch einen Schritt weiter: Bei einer typischen Implementierung würde es höchstwahrscheinlich eine Tabelle geben, in der die Farben anhand eines Identifizierungsschlüssels, color_list, aufgelistet werden. Der Implementierung kann eine neue Farbe hinzugefügt werden, ohne die Abfrage ändern zu müssen. Die möglicherweise endlose union -klausel kann ganz vermieden werden, indem die Abfrage folgendermaßen geändert wird:

SELECT id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
  colors
  INNER JOIN
  (select id as digit from color_list) n
  ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY id, n.digit;

Es ist jedoch wichtig, dass die IDs in der Tabelle color_list sequentiell bleiben.

0
gerrit_hoekstra

beachten Sie, dass dies ohne das Erstellen einer temporären Tabelle möglich ist

select id, substring_index(substring_index(genre, ',', n), ',', -1) as genre
from my_table
join 
(SELECT @row := @row + 1 as n FROM 
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(SELECT @row:=0) r) as numbers
  on char_length(genre) 
    - char_length(replace(genre, ',', ''))  >= n - 1
0
yael alfasi