问题描述:

I have query which concatenate strings if they belong to one group.

 SELECT e.id,

ke.value,

re.value AS re_value,

GROUP_CONCAT(g.value,', ')

FROM entry e

INNER JOIN k_ele ke ON e.id = ke.fk

INNER JOIN r_ele re ON e.id = re.fk

INNER JOIN sense s ON e.id = s.fk

INNER JOIN gloss g ON s.id = g.fk

WHERE g.lang IS NULL

GROUP BY s.id

ORDER BY re_value

But

GROUP_CONCAT(g.value,', ')

is giving this result.

affectionate relationship, affectionate relationship, affectionate relationship, affectionate relationship, loving relationship, loving relationship, loving relationship, loving relationship

As you can see there are duplications in concatenation. How to avoid duplications in concatenations?

网友答案:
GROUP_CONCAT(DISTINCT g.value)
网友答案:

You have to remove the duplicates before applying the GROUP_CONCAT, which typically requires a subquery:

SELECT a, GROUP_CONCAT(b)
FROM (SELECT DISTINCT a, b
      FROM MyTable)
GROUP BY a
相关阅读:
Top