不能在mysql函数中使用别名作为ORDER BY子句的一部分

If I execute the following query it works fine.

如果我执行以下查询，它可以正常工作。

SELECT 
CASE WHEN description LIKE '%a%' THEN 'A'
        WHEN description LIKE '%b%' THEN 'B'
        ELSE 'C'
    END AS foo
,COUNT(*) AS cnt
FROM product p
GROUP BY foo
ORDER BY foo;

The result is something like

结果是这样的

foo  |  cnt
-----------
A    |  809
B    |   29
C    |   55

However, the following query gives an error. Note that I only changed the last line.

但是，下面的查询会出现错误。注意，我只更改了最后一行。

SELECT 
CASE WHEN description LIKE '%a%' THEN 'A'
        WHEN description LIKE '%b%' THEN 'B'
        ELSE 'C'
    END AS foo
,COUNT(*) AS cnt
FROM product p
GROUP BY foo
ORDER BY FIELD(foo, 'A', 'B', 'C');    -- Different line

SQL Error (1054): Unknown column 'foo' in 'order clause'

SQL Error(1054):“order子句”中的未知列“foo”

Why is that?

这是为什么呢?

I'musing MySQL version 5.0.22

我'musing 5.0.22 MySQL版本

UPDATE:

更新:

just to expand, I see the same behaviour if I leave out the grouping, that is:

扩展一下，如果我省略了分组，我看到了同样的行为，那就是:

SELECT 
CASE WHEN description LIKE '%a%' THEN 'A'
        WHEN description LIKE '%b%' THEN 'B'
        ELSE 'C'
    END AS foo
FROM product p
ORDER BY foo;

(runs fine)

(细)

vs

vs

SELECT 
CASE WHEN description LIKE '%a%' THEN 'A'
        WHEN description LIKE '%b%' THEN 'B'
        ELSE 'C'
    END AS foo
FROM product p
ORDER BY FIELD(foo, 'A', 'B', 'C');    -- Different line

(error)

(错误)

4 个解决方案

#1

The reason why that happens is because MySQL was processing ORDER BY in earlier versions (apparently including 5.0) is the following way:

之所以会出现这种情况，是因为MySQL在早期版本(显然包括5.0)中处理订单的方式如下:

1. If the entire ORDER BY clause is an integer, sort by the corresponding element of the projection clause (ORDER BY 2 sorts by the second element of the project clause)

   如果整个ORDER BY子句是一个整数，那么按投影子句的相应元素(ORDER BY 2按project子句的第二个元素排序)排序

2. If the entire ORDER BY clause is an identifier, search for a projection list element with such an alias, or, if none found, for a column with such a name, and sort by that.

   如果整个ORDER BY子句是一个标识符，那么搜索具有这样一个别名的投影列表元素，或者，如果没有找到，搜索具有这样一个名称的列，并对其进行排序。

3. Otherwise, parse the expression. Only column names, but not aliases, are visible during parsing.

   否则,解析表达式。在解析期间，只能看到列名，而不能看到别名。

So in your first query you get to the second case, hence you see the alias, in your second query you get to the third case, and hence do not see it.

在你的第一个查询中，你得到了第二种情况，因此你看到了别名，在你的第二个查询中，你得到了第三种情况，因此你看不到它。

UPDATE: Seems like PostgreSQL has exactly the same behavior. Look at answer by Peter Eisentraut here [ ORDER BY Alias not working ]

更新:似乎PostgreSQL有完全相同的行为。看看Peter Eisentraut的答案(别名不工作)

He summarizes the same thing I said, but for PostgreSQL. Order By can have either an alias (output column), or an integer, or an expression referencing input columns.

他总结了我说过的话，但是对于PostgreSQL。Order By可以有别名(输出列)、整数或引用输入列的表达式。

#2

If you want to group by the alias then put your query in a derived table

如果希望以别名进行分组，则将查询放在派生表中。

SELECT foo, count(*) cnt FROM (
    SELECT 
    CASE WHEN description LIKE '%a%' THEN 'A'
        WHEN description LIKE '%b%' THEN 'B'
        ELSE 'C'
    END AS foo
    FROM product p
) t1
GROUP BY foo
ORDER BY FIELD(foo, 'A', 'B', 'C');    -- Different line

#3

Your error occures because you cannot use alias within the field method.

出现错误是因为无法在field方法中使用别名。

Use 1 insted of foo. Like this:

使用1 insted of foo。是这样的:

ORDER BY FIELD(1, 'A', 'B', 'C');    -- Different line

#4

If you know the ordinal position, you should be able to group by that such as..

如果你知道序数的位置，你应该能像……

select *
   from
      ( SELECT 
              CASE WHEN description LIKE '%a%' THEN 'A'
                   WHEN description LIKE '%b%' THEN 'B'
                   ELSE 'C' END AS foo
              ,COUNT(*) AS cnt
           FROM product p
           GROUP BY 1 ) PreQuery
   ORDER BY 
      FIELD(foo, 'A', 'B', 'C');

I know the order by might not work as it is part of a function, so I have wrapped it... OR, you would just need to have your dynamically constructed query build the case/when condition and have it pasted into each component

我知道order by可能不能工作，因为它是函数的一部分，所以我已经包装了它……或者，您只需要动态构建查询来构建case/when条件并将其粘贴到每个组件中

SELECT 
      CASE WHEN description LIKE '%a%' THEN 'A'
           WHEN description LIKE '%b%' THEN 'B'
           ELSE 'C' END AS foo
      ,COUNT(*) AS cnt
   FROM 
      product p
   GROUP BY 
      CASE WHEN description LIKE '%a%' THEN 'A'
           WHEN description LIKE '%b%' THEN 'B'
           ELSE 'C' END
   ORDER BY 
      FIELD( CASE WHEN description LIKE '%a%' THEN 'A'
                  WHEN description LIKE '%b%' THEN 'B'
                  ELSE 'C' END, 'A', 'B', 'C');