从 MySQL 中的字符串数组中删除重复的单词

debugcn 发表于 Dev

哔叽

我有 2 个 varchar 列的表 - col_name1 和 col_name2

(1, 'hello world', 'hello test'),
(2, 'the stack over', 'over the flow'),
(3, 'hello from my sql fiddle', 'hello my sql');

见 SQLFIDDLE http://sqlfiddle.com/#!9/cf90c1/1

我正在寻找一种方法来在两列中找到重复的单词并从 col_name1 中删除这样的单词。

这意味着在 Mysql 操作之后 + UPDATE + SET - col_name1 应该包含像下面这样的词

(1, 'world', 'hello test'),
(2, 'stack', 'over the flow'),
(3, 'from fiddle', 'hello my sql');

用户8406805

这是您的问题的解决方案：

解决您的问题语句的 SQL：

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    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
    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
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    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
    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
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

以下是从样本数据到预期输出的示例：

mysql> create table table_name(id int, col_name1 varchar(200),col_name2 varchar(200));
Query OK, 0 rows affected (0.36 sec)

mysql> insert into table_name values
    -> (1, 'hello world', 'hello test'),
    -> (2, 'the stack over', 'over the flow'),
    -> (3, 'hello from my sql fiddle', 'hello my sql');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update table_name x3
    -> join (
    -> select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     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
    ->     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
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
    -> where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
    -> FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
    -> INNER JOIN
    -> (
    ->     SELECT 1 + a.i + b.i * 10 x
    ->     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
    ->     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
    -> ) x
    -> ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
    -> group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
    -> having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
    -> group by id
    -> ) x
    -> on x3.id = x.id
    -> set x3.col_name1 = x.w;
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from table_name;
+------+-------------+---------------+
| id   | col_name1   | col_name2     |
+------+-------------+---------------+
|    1 | world       | hello test    |
|    2 | stack       | over the flow |
|    3 | from fiddle | hello my sql  |
+------+-------------+---------------+
3 rows in set (0.00 sec)

希望，它会解决你的问题。祝一切顺利！！！

编辑 - 根据问题所有者的要求：处理大量单词。现在它将处理多达 10000 个单词

update table_name x3
join (
select id,replace(group_concat(w),',',' ') w from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    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
    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
    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) c
    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) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1)) x2
where not exists (select 1 from (SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) w
FROM (SELECT id,concat(col_name2,' ',col_name1) c FROM table_name) t
INNER JOIN
(
    SELECT 1 + a.i + b.i * 10 x
    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
    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
    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) c
    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) d 
) x
ON (LENGTH(t.c) +1 - LENGTH(REPLACE(t.c, ' ', ''))) >= x.x
group by id,SUBSTRING_INDEX(SUBSTRING_INDEX(t.c, ' ', x.x), ' ', -1) 
having count(1) > 1) x1 where x2.id = x1.id and x2.w = x1.w)
group by id
) x
on x3.id = x.id
set x3.col_name1 = x.w;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。