mysql concat_ws没有重复 [英] mysql concat_ws without duplicates

问题描述

我正在尝试将几个字段连接成一个字段，但只能在结果字符串中保留唯一的值。

  title_orig | title_fr | title_de | title_it 
 --------------------------------------------- ------------------------ 
 KANDAHAR | KANDAHAR | REISE NACH KANDAHAR | VIAGGO A KANDAHAR 
 SCREAM 2 | SCREAM 2 | SCREAM 2 | SCREAM 2 
  

使用 CONCAT_WS（'，'，title_orig，title_fr，title_de， title_it）AS标题我会得到

 标题
 ------- -------------------------------------------------- --- 
 KANDAHAR，KANDAHAR，REISE NACH KANDAHAR，VIAGGO A KANDAHAR 
 SCREAM 2，SCREAM 2，SCREAM 2，SCREAM 2 
  

但是我想得到

  title 
 --- --------------------------------------------- 
 KANDAHAR ，REISE NACH KANDAHAR，VIAGGO A KANDAHAR 
 SCREAM 2 
  

在PostgreSQL中，我使用了一个plperl存储过程（％seen =（）; return join（，，grep {$ _&&$ | $ {$ _} ++} @_ ）

如何摆脱MySQL中的重复项？

解决方案

p>有一个替代解决方案，使用 group_concat（），但它也很麻烦：

 UNION ALL SELECT id，title_fr FROM test 
 UNION ALL SELECT id，title_de FROM test 
 UNION ALL SELECT id，title_it FROM test 
）AS tmp 
 GROUP BY id; 
  

I am trying to concatenate a few fields into a single one, but only keep unique values in the resulting string.

Example:

title_orig | title_fr   | title_de                | title_it
---------------------------------------------------------------------
KANDAHAR   | KANDAHAR   | REISE NACH KANDAHAR     | VIAGGO A KANDAHAR
SCREAM 2   | SCREAM 2   | SCREAM 2                | SCREAM 2

With CONCAT_WS(', ', title_orig, title_fr, title_de, title_it) AS titles I would get

titles
------------------------------------------------------------
KANDAHAR, KANDAHAR, REISE NACH KANDAHAR, VIAGGO A KANDAHAR
SCREAM 2, SCREAM 2, SCREAM 2, SCREAM 2

But I would like to get

titles
------------------------------------------------
KANDAHAR, REISE NACH KANDAHAR, VIAGGO A KANDAHAR
SCREAM 2

In PostgreSQL, I used a plperl stored procedure (%seen=(); return join(", ", grep {$_ && ! $seen{$_}++} @_).

How could I get rid of the duplicates in MySQL?

解决方案

There is an alternative solution using group_concat(), but it is also quite cumbersome:

SELECT id, group_concat(
  DISTINCT titles
  ORDER BY titles
  SEPARATOR ', ')
FROM (
  SELECT id, title_orig AS titles FROM test
  UNION ALL SELECT id, title_fr FROM test 
  UNION ALL SELECT id, title_de FROM test 
  UNION ALL SELECT id, title_it FROM test
) AS tmp
GROUP BY id;

这篇关于mysql concat_ws没有重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！