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

考虑这个简单的查询：

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

结果是一个错误：

Invalid column name ‘NewValue’

Here’s a sql-fiddle.(将ORDER BY NewValue替换为CASE WHEN …被注释掉)

我知道在这种情况下,我可以使用ORDER BY CASE WHEN值IS NULL THEN 1 ELSE 0 END,但实际上查询更复杂,我希望尽可能地保持可读性.我是否必须使用子查询或CTE,如果是这样的话呢？

更新为Mikael Eriksson已经评论过任何表达式与别名组合是不允许的.所以即使这样(无意义的查询)也出于同样的原因：

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

结果：

Invalid column name ‘Empty’.

所以ORDER BY中允许一个别名,也可以是一个表达式,而不是两者.为什么呢实施起来太难了？因为我主要是一个程序员,我认为别名可以简单的用在表达式中.

解决方法

我还没有在SQL标准中跟踪这种行为,但是它似乎在平台之间是一致的.这是发生了什么

create table test (
  col_1 integer,col_2 integer
);

insert into test (col_1,col_2) values 
(1,3),(2,2),(3,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

别名“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

所以在你的情况下,您的查询失败,因为dbms想要将表达式中的“NewValue”解析为基表中的列名.但不是它是列别名.

PostgreSQL的

这个行为在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.

SQL Server 2008中的文档错误

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

除非我没有足够的咖啡因,这根本不是真的.此语句在SQL Server 2008和SQL Server 2012中都以“test”.“col_1”排序.

select col_1 as col_2
from test
order by col_1;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!