GROUP_CONCAT() 上的 MySQL DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3083499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL DISTINCT on a GROUP_CONCAT()
提问by user371990
I am doing SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table. Sample data below:
我在做SELECT GROUP_CONCAT(categories SEPARATOR ' ') FROM table。示例数据如下：
categories
----------
test1 test2 test3
test4
test1 test3
test1 test3
However, I am getting test1 test2 test3 test4 test1 test3back and I would like to get test1 test2 test3 test4back. Any ideas?
然而，我test1 test2 test3 test4 test1 test3回来了，我想test1 test2 test3 test4回来。有任何想法吗？
Many thanks!
非常感谢！
回答by Naktibalda
GROUP_CONCAThas DISTINCT attribute:
GROUP_CONCAT具有 DISTINCT 属性：
SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC SEPARATOR ' ') FROM table
回答by Salil
回答by Goshika Mahesh
DISTINCT: will gives you unique values.
DISTINCT: 会给你独特的价值。
SELECT GROUP_CONCAT(DISTINCT(categories )) AS categories FROM table
回答by fthiella
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 test1and test3are 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 聚合函数，使用 DISTINCT 子句：
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.
请在此处查看小提琴。
回答by Sainath
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
回答by Mohideen bin Mohammed
You can simply add DISTINCTin front.
您可以简单地在前面添加DISTINCT。
SELECT GROUP_CONCAT(DISTINCT categories SEPARATOR ' ')
if you want to sort,
如果你想排序，
SELECT GROUP_CONCAT(DISTINCT categories ORDER BY categories ASC SEPARATOR ' ')
