01.CREATE TABLE table1
02. (`id` int, `value` varchar(20))
03.;
04.
05.INSERT INTO table1
06. (`id`, `value`)
07.VALUES
08. (1, 'Red,Green,Blue'),
09. (2, 'Orangered,Periwinkle'),
10. (3, 'Green,Blue,Orangered'),
11. (4, 'Red,Blue')
12.;
13.
14.DELIMITER $$
15.
16.DROP PROCEDURE IF EXISTS explode_table $$
17.CREATE PROCEDURE explode_table(bound VARCHAR(255))
18.
19.BEGIN
20.
21.DECLARE id INT DEFAULT 0;
22.DECLARE value TEXT;
23.DECLARE occurance INT DEFAULT 0;
24.DECLARE i INT DEFAULT 0;
25.DECLARE splitted_value TEXT;
26.DECLARE done INT DEFAULT 0;
27.DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
28. FROM table1
29. WHERE table1.value != '';
30.DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
31.
32.DROP TEMPORARY TABLE IF EXISTS table2;
33.CREATE TEMPORARY TABLE table2(
34.`id` INT NOT NULL,
35.`value` VARCHAR(255) NOT NULL
36.) ENGINE=Memory;
37.
38.OPEN cur1;
39. read_loop: LOOP
40. FETCH cur1 INTO id, value;
41. IF done THEN
42. LEAVE read_loop;
43. END IF;
44.
45. SET occurance = (SELECT LENGTH(value)
46. - LENGTH(REPLACE(value, bound, ''))
47. +1);
48. SET i=1;
49. WHILE i <= occurance DO
50. SET splitted_value =
51. (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
52. LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
53.
54. INSERT INTO table2 VALUES (id, splitted_value);
55. SET i = i + 1;
56.
57. END WHILE;
58. END LOOP;
59.
60. SELECT t.value `สี`, COUNT(t.value) `จำนวน` FROM (SELECT * FROM table2) t GROUP BY t.value;
61. CLOSE cur1;
62.END; $$
63.
64.
65.CALL explode_table(',');
Step 2 - GROUP BY
)
