删除动态ROW_NUMBER（）OVER重复ORDER BY语句(Removing duplicat

我从SQL语句下面的代码片段

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语句？

Answer 1:

你可以这样做的一个方法是在不同的层面来定义列别名，所以你可以两次引用它，而无需重复表达。

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这虽然不是。这种捕捉的所有查询会杀死获得可以使用索引来避免排序好的计划的想法。

Answer 2:

我不知道你是否会认为这是简单的。它采用算术运算得到的结果，基于一个事实，即下降列数是总数减去上升：

((case when @SortBy like '%DESC' then 1 else -1 end) *
 (case when @SortBy like '%DESC'
       then count(*) over ()
       else 0
  end) -
 (ROW_NUMBER() OVER
      (ORDER BY 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)
                     ELSE NULL
                 END)
 )
)

它确实列的列表限制到一个case语句。

文章来源: Removing duplication in dynamic ROW_NUMBER() OVER ORDER BY statement