为什么我不能使用CASE在ORDER BY中引用列别名？

Sorry if this a duplicate, but i haven't found one. Why can't i use my column alias defined in the SELECT from the ORDER BY when i use CASE?

对不起，如果这个重复，但我还没有找到一个。当我使用CASE时，为什么我不能使用ORDER BY中SELECT中定义的列别名？

Consider this simple query:

考虑这个简单的查询：

SELECT NewValue=CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END
FROM dbo.TableA
ORDER BY CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END

The result is an error:

结果是错误：

Invalid column name 'NewValue'

列名称“NewValue”无效

Here's a sql-fiddle. (Replace the ORDER BY NewValue with the CASE WHEN... that´'s commented out)

这是一个sql-fiddle。（将ORDER BY NewValue替换为CASE WHEN ......已被注释掉）

I know i can use ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END like here in this case but actually the query is more complex and i want to keep it as readable as possible. Do i have to use a sub-query or CTE instead, if so why is that so?

我知道我可以使用ORDER BY CASE，因为在这种情况下，值为IS NULL，然后是1 ELSE 0 END，但实际上查询更复杂，我希望尽可能保持可读性。我是否必须使用子查询或CTE，如果是这样，为什么会这样？

Update as Mikael Eriksson has commented any expression in combination with an alias is not allowed. So even this (pointless query) fails for the same reason:

更新为Mikael Eriksson评论任何表达式与别名组合是不允许的。所以即使这个（毫无意义的查询）失败也是出于同样的原因：

SELECT '' As Empty
FROM dbo.TableA
ORDER BY Empty + ''

Result:

结果：

Invalid column name 'Empty'.

列名称“空”无效。

So an alias is allowed in an ORDER BY and also an expression but not both. Why, is it too difficult to implement? Since i'm mainly a programmer i think of aliases as variables which could simple be used in an expression.

因此，ORDER BY和表达式中允许使用别名，但不能同时使用两者。为什么，实施起来太难了？由于我主要是程序员，我认为别名是变量，可以简单地用在表达式中。

2 个解决方案

#1

This has to do with how a SQL dbms resolves ambiguous names.

这与SQL dbms如何解析模糊名称有关。

I haven't yet tracked down this behavior in the SQL standards, but it seems to be consistent across platforms. Here's what's happening.

我还没有在SQL标准中追踪这种行为，但它似乎在各个平台上都是一致的。这是正在发生的事情。

create table test (
  col_1 integer,
  col_2 integer
);

insert into test (col_1, col_2) values 
(1, 3), 
(2, 2), 
(3, 1);

Alias "col_1" as "col_2", and use the alias in the ORDER BY clause. The dbms resolves "col_2" in the ORDER BY as an alias for "col_1", and sorts by the values in "test"."col_1".

别名“col_1”为“col_2”，并使用ORDER BY子句中的别名。 dbms将ORDER BY中的“col_2”解析为“col_1”的别名，并按“test”中的值进行排序。“col_1”。

select col_1 as col_2
from test
order by col_2;

col_2
--
1
2
3

Again, alias "col_1" as "col_2", but use an expression in the ORDER BY clause. The dbms resolves "col_2" not as an alias for "col_1", but as the column "test"."col_2". It sorts by the values in "test"."col_2".

同样，别名“col_1”为“col_2”，但在ORDER BY子句中使用表达式。 dbms解析“col_2”不是“col_1”的别名，而是“test”。“col_2”列。它按“test”中的值排序。“col_2”。

select col_1 as col_2
from test
order by (col_2 || '');

col_2
--
3
2
1

So in your case, your query fails because the dbms wants to resolve "NewValue" in the expression as a column name in a base table. But it's not; it's a column alias.

因此，在您的情况下，您的查询失败，因为dbms想要将表达式中的“NewValue”解析为基表中的列名。但事实并非如此;它是列别名。

PostgreSQL

PostgreSQL的

This behavior is documented in PostgreSQL in the section Sorting Rows. Their stated rationale is to reduce ambiguity.

PostgreSQL在Sorting Rows部分中记录了此行为。他们陈述的理由是减少歧义。

Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

请注意，输出列名称必须独立，也就是说，它不能在表达式中使用 - 例如，这不正确：

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

This restriction is made to reduce ambiguity. There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column's name.

这种限制是为了减少歧义。如果ORDER BY项是一个简单的名称，可以匹配输出列名称或表表达式中的列，则仍然存在歧义。在这种情况下使用输出列。如果您使用AS重命名输出列以匹配其他表列的名称，这只会造成混淆。

Documentation error in SQL Server 2008

SQL Server 2008中的文档错误

A slightly different issue with respect to aliases in the ORDER BY clause.

关于ORDER BY子句中的别名的问题略有不同。

If column names are aliased in the SELECT list, only the alias name can be used in the ORDER BY clause.

如果列名在SELECT列表中有别名，则只能在ORDER BY子句中使用别名。

Unless I'm insufficiently caffeinated, that's not true at all. This statement sorts by "test"."col_1" in both SQL Server 2008 and SQL Server 2012.

除非我没有足够的咖啡因，否则这根本不是真的。此语句在SQL Server 2008和SQL Server 2012中按“test”。“col_1”排序。

select col_1 as col_2
from test
order by col_1;

#2

-1

You could try something like:

你可以尝试类似的东西：

select NewValue from (
  SELECT (CASE WHEN Value IS NULL THEN '<Null-Value>' ELSE Value END ) as NewValue, 
  ( CASE WHEN NewValue='<Null-Value>' THEN 1 ELSE 0 END) as ValOrder
  FROM dbo.TableA
  GROUP BY Value
) t
ORDER BY ValOrder

#1