为什么我不能使用 CASE 在 ORDER BY 中引用列别名? [英] Why can't i refer to a column alias in the ORDER BY using CASE?

问题描述

对不起，如果这是重复的，但我还没有找到.为什么我在使用 CASE 时不能使用在 ORDER BY 的 SELECT 中定义的列别名?

考虑这个简单的查询:

SELECT NewValue=CASE WHEN Value IS NULL THEN ''ELSE 值结束从 dbo.TableA当 NewValue='' 时按情况排序然后 1 其他 0 结束

结果报错:

<块引用>

无效的列名NewValue"

这是一个 sql-fiddle.(替换 ORDER BYNewValue 带有注释掉的 CASE WHEN...)

我知道我可以使用 ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END 就像 此处 在这种情况下，但实际上查询更复杂，我希望尽可能保持其可读性.我是否必须使用子查询或 CTE，如果是这样，为什么会这样?

更新，因为 Mikael Eriksson 评论过任何 表达式与别名的组合是不允许的.所以即使这个(毫无意义的查询)也会因为同样的原因而失败:

SELECT '' 为空从 dbo.TableA按空排序 + ''

结果:

<块引用>

无效的列名空".

因此，ORDER BY 和表达式中允许使用别名，但不能同时使用.为什么，实施起来太难了?由于我主要是一名程序员，因此我将别名视为可以在表达式中简单使用的变量.

解决方案

这与 SQL dbms 如何解决歧义名称有关.

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

创建表测试(col_1 整数，col_2 整数);插入测试 (col_1, col_2) 值(1, 3),(2, 2),(3, 1);

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

选择 col_1 作为 col_2从测试按 col_2 排序；

<前>col_2——123

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

选择 col_1 作为 col_2从测试按 (col_2 || '') 排序；

<前>col_2——321

因此，在您的情况下，您的查询失败，因为 dbms 想要将表达式中的NewValue"解析为基表中的列名.但事实并非如此；这是一个列别名.

PostgreSQL

PostgreSQL 中的排序行.他们陈述的理由是减少歧义.

<块引用>

请注意，输出列名称必须是独立的，也就是说，它不能用于表达式中——例如，这是不正确的:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;-  错误的

<块引用>

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

SQL Server 2008 中的文档错误

关于稍微问题rel="nofollow">ORDER BY 子句中的别名.

<块引用>

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

除非我的咖啡因摄入不足，否则这根本不是真的.此语句在 SQL Server 2008 和 SQL Server 2012 中均按test".col_1"排序.

选择 col_1 作为 col_2从测试按 col_1 排序；

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?

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'

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

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?

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:

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.

解决方案

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

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.

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".

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".

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.

PostgreSQL

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

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.

Documentation error in SQL Server 2008

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

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

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.

select col_1 as col_2
from test
order by col_1;

这篇关于为什么我不能使用 CASE 在 ORDER BY 中引用列别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！