mysql 函数 别名_不能在MySQL函数中使用别名作为ORDER BY子句...

如果我执行以下查询,则工作正常.

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;

结果是像

foo | cnt

-----------

A | 809

B | 29

C | 55

但是,以下查询给出了错误.请注意,我只更改了最后一行.

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’

这是为什么？

我正在浏览MySQL 5.0.22版

更新：

只是为了扩展,如果省略分组,我将看到相同的行为,即：

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;

(运行正常)

与

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

(错误)