mysql合并两个varchar列删除重复的单词

w41d8nur  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(335)

我有两个varchar列的表-col\u name1和col\u name2加上空的varchar col\u name3

  1. (1, 'hello world', 'hello test', ''),
  2. (2, 'hello from my sql fiddle', 'hello my sql', '');

请参见sqlfiddle
我正在寻找一种方法,将两个varchar列合并为一个,但删除重复的单词。这意味着一个新的col\u name3应该只包含如下所示的唯一单词

  1. (1, 'hello world', 'hello test', 'world test'),
  2. (2, 'hello from my sql fiddle', 'hello my sql', 'from fiddle');
5fjcxozz

5fjcxozz1#

以下是问题的解决方案:

  1. select id,group_concat(words) output
  2. from(
  3. SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ' ', x.cifre), ' ', -1) AS words
  4. ,count(1)
  5. FROM (SELECT id,concat(col_name1,' ',col_name2,' ',col_name3) as col FROM table_name) t
  6. INNER JOIN
  7. (
  8. SELECT 1 + a.i + b.i * 10 cifre, b.i + a.i * 10 sute
  9. FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  10. CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
  11. ) x
  12. ON (LENGTH(t.col) +1 - LENGTH(REPLACE(t.col, ' ', ''))) >= x.cifre
  13. group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ' ', x.cifre), ' ', -1)
  14. having count(1) =1) s
  15. where length(words) > 0
  16. group by id

输出:

  1. id output
  2. 1 test,world
  3. 2 fiddle,from

http://sqlfiddle.com/#!9/942f8/27号

展开查看全部

相关问题