GROUP_CONCAT()上的不同MySQL

I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. Sample data below:

我正在从表中选择GROUP_CONCAT(类别分隔符')。样本数据如下:

categories
----------
test1 test2 test3
test4
test1 test3
test1 test3

However, I am getting test1 test2 test3 test4 test1 test3 back and I would like to get test1 test2 test3 test4 back. Any ideas?

然而，我正在得到test1 test2 test3 test4 test1 test3，我想要得到test1 test2 test3 test4。什么好主意吗?

Many thanks!

很多谢谢!

5 个解决方案

#1

275

GROUP_CONCAT has DISTINCT attribute:

GROUP_CONCAT有着独特的属性:

SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC SEPARATOR ' ') FROM table

#2

Using DISTINCT will work

使用不同的工作

SELECT GROUP_CONCAT(DISTINCT(categories) SEPARATOR ' ') FROM table

REf:- this

裁判:——这

#3

Other answers to this question do not return what the OP needs, they will return a string like:

此问题的其他答案不返回OP需要的内容，它们将返回如下字符串:

test1 test2 test3 test1 test3 test4

(notice that test1 and test3 are duplicated) while the OP wants to return this string:

(注意test1和test3是重复的)而OP想要返回这个字符串:

test1 test2 test3 test4

the problem here is that the string "test1 test3" is duplicated and is inserted only once, but all of the others are distinct to each other ("test1 test2 test3" is distinct than "test1 test3", even if some tests contained in the whole string are duplicated).

这里的问题是，字符串“test1 test3”是重复的，并且只插入一次，但是其他所有的都是不同的(“test1 test2 test3”与“test1 test3”是不同的，即使整个字符串中包含的一些测试是重复的)。

What we need to do here is to split each string into different rows, and we first need to create a numbers table:

我们需要做的是将每个字符串分割成不同的行，我们首先需要创建一个数字表:

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

then we can run this query:

然后我们可以运行这个查询:

SELECT
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(tableName.categories, ' ', numbers.n),
    ' ',
    -1) category
FROM
  numbers INNER JOIN tableName
  ON
    LENGTH(tableName.categories)>=
    LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1;

and we get a result like this:

我们得到这样的结果:

test1
test4
test1
test1
test2
test3
test3
test3

and then we can apply GROUP_CONCAT aggregate function, using DISTINCT clause:

然后我们可以使用GROUP_CONCAT聚合函数，使用不同的子句:

SELECT
  GROUP_CONCAT(DISTINCT category ORDER BY category SEPARATOR ' ')
FROM (
  SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tableName.categories, ' ', numbers.n), ' ', -1) category
  FROM
    numbers INNER JOIN tableName
    ON LENGTH(tableName.categories)>=LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1
  ) s;

Please see fiddle here.

请在这里看到小提琴。

#4

SELECT
  GROUP_CONCAT(DISTINCT (category))
FROM (
  SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(tableName.categories, ' ', numbers.n), ' ', -1) category
  FROM
    numbers INNER JOIN tableName
    ON LENGTH(tableName.categories)>=LENGTH(REPLACE(tableName.categories, ' ', ''))+numbers.n-1
  ) s;

This will return distinct values like: test1,test2,test4,test3

这将返回不同的值，如:test1、test2、test4、test3

#5

I realize this question is old, but I feel like this should be mentioned: group_concat with distinct = performance killer. If you work in small databases, you won't notice, but when it scales - it won't work very well.

我意识到这个问题已经过时了，但是我觉得应该提到这个问题:group_concat with distinct = performance killer。如果您在小型数据库中工作，您不会注意到，但是当它扩展时—它不会很好地工作。

#1

275