删除动态 ROW_NUMBER() OVER ORDER BY 语句中的重复项 [英] Removing duplication in dynamic ROW_NUMBER() OVER ORDER BY statement

问题描述

我有以下 SQL 语句片段

I have the following snippet from a SQL statements

ROW_NUMBER() OVER (ORDER BY CASE
    WHEN @SortBy = 'column1 ASC' THEN cast(column1 AS sql_variant)
    WHEN @SortBy = 'column2 ASC' THEN cast(column2 AS sql_variant)
    WHEN @SortBy = 'column3 ASC' THEN cast(column3 AS sql_variant)
    WHEN @SortBy = 'column4 ASC' THEN cast(column4 AS sql_variant)
    ELSE NULL
END ASC,
CASE
    WHEN @SortBy = 'column1 DESC' THEN cast(column1 AS sql_variant)
    WHEN @SortBy = 'column2 DESC' THEN cast(column2 AS sql_variant)
    WHEN @SortBy = 'column3 DESC' THEN cast(column3 AS sql_variant)
    WHEN @SortBy = 'column4 DESC' THEN cast(column4 AS sql_variant)
    ELSE NULL
END DESC) AS RowNumber

它可以工作，但它非常重复，有没有办法使 ASC/DESC 也动态化，这样我就不需要重复的 CASE 语句?

It works, but it's quite repetitive, is there a way to make the ASC/DESC dynamic as well, so I don't need the duplicated CASE statement?

推荐答案

您可以这样做的一种方法是在不同级别定义列别名，以便您可以在不重复表达式的情况下引用它两次.

One way you could do this would be to define the column alias at a different level so you can reference it twice without repeating the expression.

SELECT *,
       Row_number() OVER (ORDER BY 
                 CASE WHEN @SortBy LIKE '% ASC' THEN sort_col END ASC, 
                 CASE WHEN @SortBy LIKE '% DESC' THEN sort_col END DESC) AS RowNumber
FROM   YourTable
       CROSS APPLY (SELECT CASE
                             WHEN @SortBy LIKE 'column1 %' THEN Cast(column1 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column2 %' THEN Cast(column2 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column3 %' THEN Cast(column3 AS SQL_VARIANT)
                             WHEN @SortBy LIKE 'column4 %' THEN Cast(column4 AS SQL_VARIANT)
                           END) C(sort_col) 

不过，我会考虑为此使用动态 SQL.这种捕获所有查询将扼杀获得可以使用索引来避免排序的良好计划的想法.

I would consider using dynamic SQL for this instead though. This kind of catch all query will kill the idea of getting a good plan that can use indexes to avoid a sort.

这篇关于删除动态 ROW_NUMBER() OVER ORDER BY 语句中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！