如果我执行以下查询,则工作正常.
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
(错误)