問題描述
所以，這不是一般的條件排序"問題……我在這里遇到了一個相當棘手的問題.:-) 我想讓我的存儲過程為結果提供條件排序.通常，這可以通過以下方式完成:
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 數據的工作示例.
這篇關于SQL Server 窗口函數子句中的條件排序順序的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！