问题描述
所以,这不是一般的条件排序"问题……我在这里遇到了一个相当棘手的问题.:-) 我想让我的存储过程为结果提供条件排序.通常,这可以通过以下方式完成:
So, this is not your average 'conditional sort by' question... I have a rather tricky problem here. :-) I want to allow my stored procedure to offer a conditional sort order for the results. Normally this can be done in the following manner:
SELECT * INTO #ResultsBeforeSubset FROM MyTable ORDER BY CASE WHEN @SortAscending=1 THEN 'SortColumn' END ASC, CASE WHEN @SortAscending=0 THEN 'SortColumn' END DESC
我想围绕实际的 ASC/DESC 做一个 CASE 语句,但这不起作用.上述方法起作用的原因是,当@SortAscending 不等于给定值时,SQL 服务器将CASE 语句转换为常量NULL代码>.所以,如果 @SortAscending 是 0,你实际上有:
I'd like to do a CASE statement around the actual ASC/DESC, but that doesn't work. The reason the above method works is because, when @SortAscending isn't equal to the given value, SQL server translates the CASE statement into the constant NULL. So, if @SortAscending is 0, you effectively have:
ORDER BY NULL ASC, SortColumn DESC
然后,第一个排序表达式什么也不做.这是有效的,因为在常规 SELECT 语句中,您可以在 ORDER BY 子句中使用常量.
The first sort expression, then, just does nothing. This works because in a regular SELECT statement you can use constant in an ORDER BY clause.
问题是,我在存储过程中排序的时间是在包含窗口函数 ROW_NUMBER() 的 SELECT 语句期间.因此,我想将 CASE 语句放在其 OVER 子句中,如下所示:
Trouble is, the time that I'm sorting in my stored proc is during a SELECT statement which contains a windowed function ROW_NUMBER(). I therefore want to put the CASE statement inside its OVER clause, like so:
SELECT * INTO #ResultsBeforeSubset FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY CASE WHEN @SortAscending=1 THEN rowValues.[SortColumn] END ASC, CASE WHEN @SortAscending=0 THEN rowValues.[SortColumn] END DESC ) AS RowNumber, * FROM ( -- UNIONed SELECTs returning rows go here... ) rowValues ) rowValuesWithRowNum
不幸的是,当您运行存储过程时,这会导致以下错误:
Unfortunately, this causes the following error when you run the stored procedure:
Windowed functions do not support constants as ORDER BY clause expressions.
因为这是一个窗口函数的子句,所以CASE语句到常量NULL的转换是无效的.
Because this is the clause of a windowed function, the conversion of the CASE statement to the constant NULL is invalid.
谁能想出一种方法,我可以有条件地改变 UNIONed SELECT 的排序顺序,并为这些排序结果产生的每一行分配行号?我知道我可以将整个查询构造为一个字符串并作为完全动态的 SQL 执行它,但如果可能的话,我宁愿避免这种情况.
Can anyone think of a way that I can conditionally vary the sort order of UNIONed SELECTs, and assign row numbers to each row resulting from these sorted results? I know I could resort to constructing the entire query as a string and execute that as fully dynamic SQL, but I'd rather avoid that if possible.
更新: 看起来问题不是由 CASE 语句本身引起的,而是因为我只使用了CASE 语句的条件子句中的常量值.我对这种奇怪的行为提出了一个新问题 这里.
UPDATE: Looks like the problem wasn't caused by the CASE statement per se, but by the fact that I was using only constant values in the CASE statement's conditional clause. I've started up a new question on this curious behaviour here.
推荐答案
您可以在两个方向分配行号,并在外部 order by 中选择一个:
You could assign row numbers in two directions, and pick one in an outer order by:
select * from ( select row_number() over (order by SortColumn) rn1 , row_number() over (order by SortColumn) rn2 , * from @t ) as SubQueryAlias order by case when @asc=1 then rn1 end , case when @asc=0 then rn2 end desc
SE 数据的工作示例.