如何在GROUP_CONCAT()中同时使用DISTINCT与分隔符参数？

Ah, I’ve run into this exact MySQL quirk before! The error pops up because you’re trying to pass two arguments (name and ';') right after DISTINCT in GROUP_CONCAT—MySQL doesn’t allow that. The DISTINCT keyword here only applies to the specific expression you’re aggregating, and you can’t mix it with a separator like that.

The Correct Syntax #

Instead of gluing the separator to the expression with DISTINCT, use the SEPARATOR keyword to define your delimiter separately. This way, DISTINCT only acts on the name field (to strip out duplicates), and MySQL handles the separation properly:

SELECT Town, GROUP_CONCAT(DISTINCT name SEPARATOR ';') 
FROM MY_TABLE 
WHERE action IS NOT NULL 
GROUP BY Town;

Why This Works #

• DISTINCT name: First removes any duplicate name values within each Town group.
• SEPARATOR ';': Tells MySQL to join the unique names together using a semicolon as the delimiter between entries (no trailing semicolon at the end, which is usually the desired behavior).

If You Need Trailing Semicolons (Optional) #

If your use case requires every name to end with a semicolon (including the last one), wrap the name in a CONCAT first, then apply DISTINCT:

SELECT Town, GROUP_CONCAT(DISTINCT CONCAT(name, ';')) 
FROM MY_TABLE 
WHERE action IS NOT NULL 
GROUP BY Town;

This will output something like Alice;Bob;Charlie; instead of Alice;Bob;Charlie.

内容的提问来源于stack exchange，提问作者Thibaud