别名在ORDER BY子句中不起作用

问题描述

这将在MySQL 5中正常工作:

This will work fine in MySQL 5:

SELECT INSTR(foo, 'Bar') as foobar
FROM Table
ORDER BY CASE WHEN foobar = 1 THEN foo END DESC;

在MySQL 4中，Bu出现错误:

Bu in MySQL 4, I get the error:

ERROR 1054 (42S22): Unknown column 'foobar' in 'order clause'

但是，如果我将该子句更改为该子句，则它将在两个版本上均适用:

However, if I change the clause to this, it will work on both versions:

SELECT INSTR(foo, 'Bar') as foobar
FROM Table
ORDER BY CASE WHEN INSTR(foo, 'Bar') = 1 THEN foo END DESC;

为确保兼容性，我是否必须始终使用第二种方法?

To ensure compatibility, do I have to always use the second way?

推荐答案

根据其他答案，您不能在CASE中使用别名.
代替使用子查询，您可以像这样直接在CASE中使用INSTR():

As per other answers, you can not use alias in CASE.
Instead of using sub-query you can direct use INSTR() in CASE like this:

SELECT INSTR(foo, 'Bar') as foobar
FROM Table
ORDER BY CASE WHEN INSTR(foo, 'Bar') = 1 THEN foo END DESC;

在使用子查询时，请注意，还必须选择foo列进行排序，否则，您将得到错误像这样

When you are using sub-query then note that you will also have to select foo column to order by it, other wise you will get an error like this

因此，带有子查询的查询应为:

So your query with sub-query should be:

SELECT * FROM
(
    SELECT foo,INSTR(foo, 'Bar') as foobar
    FROM t
) A
ORDER BY CASE WHEN foobar = 1 THEN foo END DESC;

这篇关于别名在ORDER BY子句中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！