sql将值拆分为多行

hxzsmxv2  于 2021-06-18  发布在  Mysql
关注(0)|答案(9)|浏览(581)

我有一张table:

  1. id | name
  2. 1 | a,b,c
  3. 2 | b

我想要这样的输出:

  1. id | name
  2. 1 | a
  3. 1 | b
  4. 1 | c
  5. 2 | b
t3irkdon

t3irkdon1#

如果可以创建一个数字表,其中包含从1到要拆分的最大字段的数字,则可以使用以下解决方案:

  1. select
  2. tablename.id,
  3. SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
  4. from
  5. numbers inner join tablename
  6. on CHAR_LENGTH(tablename.name)
  7. -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
  8. order by
  9. id, n

请看这里的小提琴。
如果无法创建表,则解决方案可以是:

  1. select
  2. tablename.id,
  3. SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
  4. from
  5. (select 1 n union all
  6. select 2 union all select 3 union all
  7. select 4 union all select 5) numbers INNER JOIN tablename
  8. on CHAR_LENGTH(tablename.name)
  9. -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
  10. order by
  11. id, n

这里是小提琴的一个例子。

展开查看全部
xqnpmsa8

xqnpmsa82#

这是我的解决办法

  1. -- Create the maximum number of words we want to pick (indexes in n)
  2. with recursive n(i) as (
  3. select
  4. 1 i
  5. union all
  6. select i+1 from n where i < 1000
  7. )
  8. select distinct
  9. s.id,
  10. s.oaddress,
  11. -- n.i,
  12. -- use the index to pick the nth word, the last words will always repeat. Remove the duplicates with distinct
  13. if(instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' ') > 0,
  14. reverse(substr(reverse(trim(substring_index(s.oaddress,' ',n.i))),1,
  15. instr(reverse(trim(substring_index(s.oaddress,' ',n.i))),' '))),
  16. trim(substring_index(s.oaddress,' ',n.i))) oth
  17. from
  18. app_schools s,
  19. n
展开查看全部
mwecs4sa

mwecs4sa3#

我的变量:以表名、字段名和分隔符为参数的存储过程。灵感来自posthttp://www.marcogoncalves.com/2011/03/mysql-split-column-string-into-rows/

  1. delimiter $$
  2. DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$
  3. CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),
  4. id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))
  5. BEGIN
  6. DECLARE id INT DEFAULT 0;
  7. DECLARE value VARCHAR(255);
  8. DECLARE occurrences INT DEFAULT 0;
  9. DECLARE i INT DEFAULT 0;
  10. DECLARE splitted_value VARCHAR(255);
  11. DECLARE done INT DEFAULT 0;
  12. DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM
  13. tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';
  14. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  15. SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',
  16. id_column,' id, ', value_column,' value FROM ',tablename);
  17. PREPARE stmt FROM @expr;
  18. EXECUTE stmt;
  19. DEALLOCATE PREPARE stmt;
  20. DROP TEMPORARY TABLE IF EXISTS tmp_table2;
  21. CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;
  22. OPEN cur;
  23. read_loop: LOOP
  24. FETCH cur INTO id, value;
  25. IF done THEN
  26. LEAVE read_loop;
  27. END IF;
  28. SET occurrences = (SELECT CHAR_LENGTH(value) -
  29. CHAR_LENGTH(REPLACE(value, delim, '')) + 1);
  30. SET i=1;
  31. WHILE i <= occurrences DO
  32. SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(
  33. SUBSTRING_INDEX(value, delim, i), delim, -1)));
  34. INSERT INTO tmp_table2 VALUES (id, splitted_value);
  35. SET i = i + 1;
  36. END WHILE;
  37. END LOOP;
  38. SELECT * FROM tmp_table2;
  39. CLOSE cur;
  40. DROP TEMPORARY TABLE tmp_table1;
  41. END; $$
  42. delimiter ;

用法示例(规范化):

  1. CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');
  2. CREATE TABLE interests (
  3. interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  4. interest VARCHAR(30) NOT NULL
  5. ) SELECT DISTINCT value interest FROM tmp_table2;
  6. CREATE TABLE contact_interest (
  7. contact_id INT NOT NULL,
  8. interest_id INT NOT NULL,
  9. CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),
  10. CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id)
  11. ) SELECT my_contacts.contact_id, interests.interest_id
  12. FROM my_contacts, tmp_table2, interests
  13. WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;
展开查看全部
ix0qys7i

ix0qys7i4#

最初的问题是针对mysql和sql的。下面的例子是针对mysql的新版本。不幸的是,不可能在任何sql server上使用通用查询。有些服务器不支持cte,有些服务器没有子字符串索引,还有一些服务器具有用于将字符串拆分为多行的内置函数。
---答案如下---
当服务器不提供内置功能时,递归查询很方便。它们也可能成为瓶颈。
以下查询是在mysql版本8.0.16上编写和测试的。它在版本5.7-上不起作用。旧版本不支持公共表表达式(cte),因此不支持递归查询。

  1. with recursive
  2. input as (
  3. select 1 as id, 'a,b,c' as names
  4. union
  5. select 2, 'b'
  6. ),
  7. recurs as (
  8. select id, 1 as pos, names as remain, substring_index( names, ',', 1 ) as name
  9. from input
  10. union all
  11. select id, pos + 1, substring( remain, char_length( name ) + 2 ),
  12. substring_index( substring( remain, char_length( name ) + 2 ), ',', 1 )
  13. from recurs
  14. where char_length( remain ) > char_length( name )
  15. )
  16. select id, name
  17. from recurs
  18. order by id, pos;
展开查看全部
vmpqdwk3

vmpqdwk35#

最佳实践。结果:

  1. SELECT
  2. SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
  3. FROM
  4. (
  5. SELECT @xi:=@xi+1 as help_id from
  6. (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
  7. (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
  8. (SELECT @xi:=-1) xc0
  9. ) a
  10. WHERE
  11. help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1

首先,创建一个数字表:

  1. SELECT @xi:=@xi+1 as help_id from
  2. (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
  3. (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
  4. (SELECT @xi:=-1) xc0;
  1. | help_id |
  2. | --- |
  3. | 0 |
  4. | 1 |
  5. | 2 |
  6. | 3 |
  7. | ... |
  8. | 24 |

第二,把str分开:

  1. SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('ab,bc,cd',',',help_id+1),',',-1) AS oid
  2. FROM
  3. numbers_table
  4. WHERE
  5. help_id < LENGTH('ab,bc,cd')-LENGTH(REPLACE('ab,bc,cd',',',''))+1
  1. | oid |
  2. | --- |
  3. | ab |
  4. | bc |
  5. | cd |
展开查看全部
hfwmuf9z

hfwmuf9z6#

  1. CREATE PROCEDURE `getVal`()
  2. BEGIN
  3. declare r_len integer;
  4. declare r_id integer;
  5. declare r_val varchar(20);
  6. declare i integer;
  7. DECLARE found_row int(10);
  8. DECLARE row CURSOR FOR select length(replace(val,"|","")),id,val from split;
  9. create table x(id int,name varchar(20));
  10. open row;
  11. select FOUND_ROWS() into found_row ;
  12. read_loop: LOOP
  13. IF found_row = 0 THEN
  14. LEAVE read_loop;
  15. END IF;
  16. set i = 1;
  17. FETCH row INTO r_len,r_id,r_val;
  18. label1: LOOP
  19. IF i <= r_len THEN
  20. insert into x values( r_id,SUBSTRING(replace(r_val,"|",""),i,1));
  21. SET i = i + 1;
  22. ITERATE label1;
  23. END IF;
  24. LEAVE label1;
  25. END LOOP label1;
  26. set found_row = found_row - 1;
  27. END LOOP;
  28. close row;
  29. select * from x;
  30. drop table x;
  31. END
展开查看全部
qvk1mo1f

qvk1mo1f7#

下面是我的尝试:第一个select将csv字段显示给split。使用递归cte,我们可以创建一个数字列表,该列表限制为csv字段中的字数。术语的数量只是csv字段的长度和它本身的长度之差,所有分隔符都被删除了。然后与这些数字结合,子串索引提取出这个项。

  1. with recursive
  2. T as ( select 'a,b,c,d,e,f' as items),
  3. N as ( select 1 as n union select n + 1 from N, T
  4. where n <= length(items) - length(replace(items, ',', '')))
  5. select distinct substring_index(substring_index(items, ',', n), ',', -1)
  6. group_name from N, T
bqf10yzr

bqf10yzr8#

我已经从这里引用了更改过的列名。

  1. DELIMITER $$
  2. CREATE FUNCTION strSplit(x VARCHAR(65000), delim VARCHAR(12), pos INTEGER)
  3. RETURNS VARCHAR(65000)
  4. BEGIN
  5. DECLARE output VARCHAR(65000);
  6. SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
  7. , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
  8. , delim
  9. , '');
  10. IF output = '' THEN SET output = null; END IF;
  11. RETURN output;
  12. END $$
  13. CREATE PROCEDURE BadTableToGoodTable()
  14. BEGIN
  15. DECLARE i INTEGER;
  16. SET i = 1;
  17. REPEAT
  18. INSERT INTO GoodTable (id, name)
  19. SELECT id, strSplit(name, ',', i) FROM BadTable
  20. WHERE strSplit(name, ',', i) IS NOT NULL;
  21. SET i = i + 1;
  22. UNTIL ROW_COUNT() = 0
  23. END REPEAT;
  24. END $$
  25. DELIMITER ;
展开查看全部
oogrdqng

oogrdqng9#

如果 name 列是一个json数组(如 '["a","b","c"]' ),然后可以使用json_table()对其进行解压缩(mysql 8.0.4以后提供):

  1. select t.id, j.name
  2. from mytable t
  3. join json_table(
  4. t.name,
  5. '$[*]' columns (name varchar(50) path '$')
  6. ) j;

结果:

  1. | id | name |
  2. | --- | ---- |
  3. | 1 | a |
  4. | 1 | b |
  5. | 1 | c |
  6. | 2 | b |

db fiddle视图
如果以简单的csv格式存储值,则首先需要将其转换为json:

  1. select t.id, j.name
  2. from mytable t
  3. join json_table(
  4. replace(json_array(t.name), ',', '","'),
  5. '$[*]' columns (name varchar(50) path '$')
  6. ) j

结果:

  1. | id | name |
  2. | --- | ---- |
  3. | 1 | a |
  4. | 1 | b |
  5. | 1 | c |
  6. | 2 | b |

db fiddle视图

展开查看全部

相关问题