MySQL group_concat()按case语句值排序 [英] MySQL group_concat() ordering by case statement values

问题描述

在MySQL group_concat()子句中，我试图对case语句的结果值进行排序.以下查询配置正确地对things.name进行了排序，但未在同一上下文中对'Non-US'或'Unknown'值进行排序.

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

有没有一种方法可以通过"new_name"进行排序而不使用子查询/嵌套查询?

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.

对于您的情况，ORDER BY 1应该有效.

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

这篇关于MySQL group_concat()按case语句值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！