SQL Server 窗口函数子句中的条件排序顺序

2021-09-10 00:00:00 sql tsql sql-server

所以，这不是一般的条件排序"问题……我在这里遇到了一个相当棘手的问题.:-) 我想让我的存储过程为结果提供条件排序.通常，这可以通过以下方式完成:

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 数据的工作示例.

相关文章