代码之家 › 专栏 › 技术社区 › Tim Schmelter

为什么我不能使用CASE引用ORDERBY中的列别名?

sql-order-by sql-server-2005 tsql sql

Tim Schmelter · 技术社区 · 10 年前

很抱歉,如果这是重复的,但我没有找到。为什么不能使用在 SELECT 来自 ORDER BY 当我使用 CASE ?

考虑这个简单的查询:

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

结果是一个错误:

列名“NewValue”无效

Here's a sql-fiddle. (更换 ORDER BY NewValue 使用 CASE WHEN... 已注释)

我知道我可以用 ORDER BY CASE WHEN Value IS NULL THEN 1 ELSE 0 END 喜欢 here 在这种情况下,但实际上查询更复杂,我希望尽可能保持它的可读性。我是否必须使用子查询或CTE来代替,如果是,为什么这样?

使现代化 正如Mikael Eriksson所评论的任何表达式不允许与别名组合使用。因此,即使是这种(无意义的查询)也会因为同样的原因而失败:

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

结果:

列名“Empty”无效。

因此允许在 订购者 还有一个表达式,但是 不是两者都有 。为什么,实施起来太难了吗?由于我主要是一名程序员,我认为别名是变量,可以简单地在表达式中使用。

3 回复 | 直到 10 年前

Mike Sherrill 'Cat Recall' 10 年前

这与SQLdbms如何解析不明确的名称有关。

我还没有在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” 不作为“col1”的别名,但作为列“test”。“col_2”。它根据“测试”中的值进行排序。“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 。他们陈述的理由是减少歧义。

注意,输出列名必须独立,即不能在表达式中使用,例如不对的:

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

这种限制是为了减少歧义。如果ORDERBY项是可以与输出列名或表表达式中的列匹配的简单名称,则仍然存在歧义。在这种情况下使用输出列。如果使用AS重命名输出列以匹配其他表列的名称,这只会导致混淆。

SQL Server 2008中的文档错误

A. 轻微地 不同的问题 aliases in the ORDER BY clause .

如果列名在SELECT列表中使用别名,则ORDERBY子句中只能使用别名。

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

select col_1 as col_2
from test
order by col_1;

hsn 3 年前

这一限制似乎与另一个限制有关,即“列别名不能在同一列中引用” SELECT list”。例如,此查询:

SELECT Col1 AS ColAlias1 FROM T ORDER BY ColAlias1

可以翻译为:

SELECT Col1 AS ColAlias1 FROM T ORDER BY 1

这是一个法律问题。但此查询:

SELECT Col1 AS ColAlias1 FROM T ORDER BY ColAlias1 + ' '

应翻译为:

SELECT Col1 AS ColAlias1, ColAlias1 + ' ' FROM T ORDER BY 2

这将引发错误:

“字段列表”中的未知列“ColAlias1”

最后,这些似乎是因为SQL标准行为,在实现中并非不可能。更多信息请访问: Here

注意:最后一个查询可以由 MS Access 没有错误,但会引发上述错误 SQL Server .

-1

Chrisky 10 年前

您可以尝试以下操作:

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