mysql group语句可以排序_MySQL group_concat（）按case语句值排序

In a MySQL group_concat() clause, I'm trying to order the resulting values of a case statement. The following query configuration properly orders things.name but does not order the 'Non-US' or 'Unknown' values within the same context.

SELECT

things.id

,group_concat(DISTINCT

CASE

WHEN things.name <> 'United States' THEN 'Non-US'

WHEN things.name IS NULL THEN 'Unknown'

ELSE things.name

END

ORDER BY name SEPARATOR ', ')

FROM things

GROUP BY things.id

I want to do something like this, but it's not working:

SELECT

things.id

,group_concat(DISTINCT

(CASE

WHEN things.name <> 'United States' THEN 'Non-US'

WHEN things.name IS NULL THEN 'Unknown'

ELSE things.name

END) AS new_name

ORDER BY new_name SEPARATOR ', ')

FROM things

GROUP BY things.id

Is there a way to sort by "new_name" without using sub-queries/ nested queries?

解决方案

You can accomplish this by ordering by column position instead of column name.

For your case ORDER BY 1 should work.

SELECT

things.id

,group_concat(DISTINCT

CASE

WHEN things.name <> 'United States' THEN 'Non-US'

WHEN things.name IS NULL THEN 'Unknown'

ELSE things.name

END

ORDER BY 1 SEPARATOR ', ')

FROM things

GROUP BY things.id