在询问
another question时,我发现sql Server(在2005年和2008年都发生过)在窗口函数的子句中处理CASE语句时似乎有奇怪的不一致行为.以下代码给出了错误：
declare @t table (SortColumn int)
insert @t values (1),(2),(3)
declare @asc bit
set @asc = 0
select row_number() over (order by
case when 1=1 then SortColumn end asc,case when 1=0 then SortColumn end desc) RowNumber,*
from @t
错误是窗口函数不支持常量作为ORDER BY子句表达式.我认为这是因为case语句可能会计算为NULL,这是一个常量.正如可能预期的那样,此代码也会出现相同的错误：
declare @t table (SortColumn int)
insert @t values (1),(3)
declare @asc bit
set @asc = 0
select row_number() over (order by
NULL asc,NULL desc) RowNumber,*
from @t
……大概是出于同样的原因.但是,此代码不会出错：
declare @t table (SortColumn int)
insert @t values (1),(3)
declare @asc bit
set @asc = 0
select row_number() over (order by
case when @asc=1 then SortColumn end asc,case when @asc=0 then SortColumn end desc) RowNumber,*
from @t
与第一个代码块的唯一区别在于我已将其中一个case语句的条件操作数移动到变量@asc中.这现在工作正常.但是为什么呢？ case语句仍然可以计算为NULL,这是一个常量,所以它不应该工作……但确实如此.这是一致的,还是微软的特殊情况？
所有这些behvaIoUr可以通过玩this query来检查.
更新：此限制不仅适用于OVER子句(尽管它们确实给出了不同的错误) – 它适用于自sql Server 2005. Here’s a query以来的所有ORDER BY子句,它们也显示了使用常规SELECT的ORDER BY子句的限制.
解决方法
联机书籍表明“排序列可以包含表达式,但当数据库处于sql Server(90)兼容模式时,表达式无法解析为常量.”但它并没有定义“常数”.
从思考它和一些实验来看,这似乎很清楚,这意味着一个表达式,在编译时可以成功地计算文字常量值.
/*Works - Constant at run time but sql Server doesn't do variable sniffing*/ DECLARE @Foo int SELECT ROW_NUMBER() OVER (ORDER BY @Foo) FROM master..spt_values /*Works - Constant folding not done for divide by zero*/ SELECT ROW_NUMBER() OVER (ORDER BY $/0) FROM master..spt_values /*Fails - Windowed functions do not support constants as ORDER BY clause expressions.*/ SELECT ROW_NUMBER() OVER (ORDER BY $/1) FROM master..spt_values
