删除动态ROW_NUMBER（）OVER ORDER BY语句中的重复项
技术问答
420 人阅读
|
0 人回复
|
2023-09-12
|
|
我有一条来自SQL语句的以下代码段" h) D& ~, n \% h/ r4 F' \+ q
ROW_NUMBER() OVER (ORDER BY CASE' a' h1 y9 i( b* g& z# Q
WHEN @SortBy = 'column1 ASC' THEN cast(column1 AS sql_variant)
2 A8 ^- m2 ]2 `1 `4 }2 r WHEN @SortBy = 'column2 ASC' THEN cast(column2 AS sql_variant)3 y$ C: l$ M+ K6 B" q
WHEN @SortBy = 'column3 ASC' THEN cast(column3 AS sql_variant). m3 D# f" ^2 ~
WHEN @SortBy = 'column4 ASC' THEN cast(column4 AS sql_variant); i8 G2 Y$ t1 {
ELSE NULL8 q* W' M# X/ V& C
END ASC,* s9 S8 Q- j$ P$ o1 n
CASE
& \7 H2 `. _; G7 o" y7 J- V WHEN @SortBy = 'column1 DESC' THEN cast(column1 AS sql_variant)5 B1 Z( ^* l1 J& u
WHEN @SortBy = 'column2 DESC' THEN cast(column2 AS sql_variant)
, A6 p6 a. @2 H, e WHEN @SortBy = 'column3 DESC' THEN cast(column3 AS sql_variant)2 ^ c# y$ d! ?* L- g
WHEN @SortBy = 'column4 DESC' THEN cast(column4 AS sql_variant)1 ]. |; q! H' H& c6 B1 t1 L
ELSE NULL
! R( D, c, W* z4 }: L5 b" G# `' @END DESC) AS RowNumber! M! S2 _, u# O% b! _4 m9 a
它有效，但是非常重复，是否也可以使ASC / DESC动态化，所以我不需要重复的CASE语句？& h7 c* D4 Y- [+ J/ k
" B, u+ L6 P. [9 P解决方案: |
|
|
|
|
|
|
|
|
|
