连接行上的mysql GROUP_CONCAT DISTINCT

Question

我有一个像这样的查询

SELECT
 producttemp.*,
 GROUP_CONCAT(colors.id) as color,
 GROUP_CONCAT(DISTINCT upc.UPC) as upc
FROM producttemp
INNER JOIN productcolor
 ON productcolor.productid=producttemp.id
INNER JOIN upc
 ON upc.productid=producttemp.id
INNER JOIN colors
 ON colors.id=productcolor.colorid
INNER JOIN categorie
 ON categorie.id = producttemp.productcategorie
GROUP BY producttemp.id
LIMIT 5

由于某种原因或其他原因，我在结果字段“颜色”中得到重复值，即“1,2,1,2”。为什么 DISTINCT 对连接的行不起作用？是否有机会变通，或者我是否遗漏了一点？

Answer 1

在 colors.id 上尝试 DISTINCT::

SELECT
 producttemp.*,
 GROUP_CONCAT(DISTINCT colors.id) as color,
 GROUP_CONCAT(DISTINCT upc.UPC) as upc
FROM producttemp
INNER JOIN productcolor
 ON productcolor.productid=producttemp.id
INNER JOIN upc
 ON upc.productid=producttemp.id
INNER JOIN colors
 ON colors.id=productcolor.colorid
INNER JOIN categorie
 ON categorie.id = producttemp.productcategorie
GROUP BY producttemp.id
LIMIT 5