blocks|key|1285911|text|您可以拥有两个几乎相同的ORDER+BY项，一个ASC和一个DESC，并扩展CASE语句，使它们中的一个始终等于单个值：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1285912|ORDER+BY
++++++CASE+WHEN+@OrderDirection+=+0+THEN+1
++++++ELSE
+++++++++++CASE+WHEN+@OrderByColumn+=+'AddedDate'+THEN+CONVERT(varchar(50),+AddedDate)
++++++++++++++++WHEN+@OrderByColumn+=+'Visible'+THEN+CONVERT(varchar(2),+Visible)
++++++++++++++++WHEN+@OrderByColumn+=+'AddedBy'+THEN+AddedBy
++++++++++++++++WHEN+@OrderByColumn+=+'Title'+THEN+Title
+++++++++++END
++++++END+ASC,
++++++CASE+WHEN+@OrderDirection+=+1+THEN+1
++++++ELSE
+++++++++++CASE+WHEN+@OrderByColumn+=+'AddedDate'+THEN+CONVERT(varchar(50),+AddedDate)
++++++++++++++++WHEN+@OrderByColumn+=+'Visible'+THEN+CONVERT(varchar(2),+Visible)
++++++++++++++++WHEN+@OrderByColumn+=+'AddedBy'+THEN+AddedBy+++++++++++
++++++++++++++++WHEN+@OrderByColumn+=+'Title'+THEN+Title
+++++++++++END
++++++END+DESC|code-block|syntax|javascript|1285913|entityMap^0|C|8|O|3|U|4|12|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

You could have two near-identical <code>ORDER BY</code> items, one <code>ASC</code> and one <code>DESC</code>, and extend your <code>CASE</code> statement to make one or other of them always equal a single value:

<pre><code>ORDER BY
 CASE WHEN @OrderDirection = 0 THEN 1
 ELSE
 CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
 WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
 WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
 WHEN @OrderByColumn = 'Title' THEN Title
 END
 END ASC,
 CASE WHEN @OrderDirection = 1 THEN 1
 ELSE
 CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
 WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
 WHEN @OrderByColumn = 'AddedBy' THEN AddedBy 
 WHEN @OrderByColumn = 'Title' THEN Title
 END
 END DESC
</code></pre>

blocks|key|1285977|text|您可以使用ROW_NUMBER来简化这种情况，它可以对数据进行排序，并有效地将其转换为方便的整数格式。尤其是因为问题被标记为SQL+Server2005|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1285978|它还可以轻松地进行扩展，以处理第二和第三排序|1285979|我使用multiplier再次简化了实际的select语句，并减少了ORDER+BY中RBAR求值的机会|1285980|DECLARE+@multiplier+int;

SELECT+@multiplier+=+CASE+@Direction+WHEN+1+THEN+-1+ELSE+1+END;

SELECT
+++++Columns+you+actually+want
FROM
++++(
++++SELECT
+++++++++Columns+you+actually+want,
+++++++++ROW_NUMBER()+OVER+(ORDER+BY+AddedDate)+AS+AddedDateSort,
+++++++++ROW_NUMBER()+OVER+(ORDER+BY+Visible)+AS+VisibleSort,
+++++++++ROW_NUMBER()+OVER+(ORDER+BY+AddedBy)+AS+AddedBySort,
+++++++++ROW_NUMBER()+OVER+(ORDER+BY+Title)+AS+TitleSort
++++FROM
+++++++++myTable
++++WHERE
+++++++++MyFilters...
++++)+foo
ORDER+BY
+++++CASE+@OrderByColumn
++++++++WHEN+'AddedDate'+THEN+AddedDateSort
++++++++WHEN+'Visible'+THEN+VisibleSort++++
++++++++WHEN+'AddedBy'+THEN+AddedBySort
++++++++WHEN+'Title'+THEN+TitleSort
+++++END+*+@multiplier;|code-block|syntax|javascript|1285981|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can simplify the CASE by using ROW_NUMBER which sorts your data and effectively converts it into a handy integer format. Especially since the question is tagged SQL Server 2005

This also expands easily enough to deal with secondary and tertiary sorts

I've used multiplier to again simplify the actual select statement and reduce the chance of RBAR evaluation in the ORDER BY

<pre><code>DECLARE @multiplier int;

SELECT @multiplier = CASE @Direction WHEN 1 THEN -1 ELSE 1 END;

SELECT
 Columns you actually want
FROM
 (
 SELECT
 Columns you actually want,
 ROW_NUMBER() OVER (ORDER BY AddedDate) AS AddedDateSort,
 ROW_NUMBER() OVER (ORDER BY Visible) AS VisibleSort,
 ROW_NUMBER() OVER (ORDER BY AddedBy) AS AddedBySort,
 ROW_NUMBER() OVER (ORDER BY Title) AS TitleSort
 FROM
 myTable
 WHERE
 MyFilters...
 ) foo
ORDER BY
 CASE @OrderByColumn
 WHEN 'AddedDate' THEN AddedDateSort
 WHEN 'Visible' THEN VisibleSort 
 WHEN 'AddedBy' THEN AddedBySort
 WHEN 'Title' THEN TitleSort
 END * @multiplier;
</code></pre>

blocks|key|1500907|text|这对我来说很好-+(where，order+by，direction，Pagination)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1500908|parameters

@orderColumn++int+,
@orderDir++varchar(20),
@start++int+,
@limit++int


select+*+from+items

order+by+

CASE+WHEN+@orderColumn+=+0+AND+@orderdir+=+'desc'+THEN+items.[CategoryName]+END+DESC,++++
CASE+WHEN+@orderColumn+=+0+AND+@orderdir+=+'asc'+THEN+items.[CategoryName]+END+ASC,++++
CASE+WHEN+@orderColumn+=+1+AND+@orderdir+=+'desc'+THEN+items.[CategoryValue]+END+DESC,
CASE+WHEN+@orderColumn+=+1+AND+@orderdir+=+'asc'+THEN+items.[CategoryValue]+END+ASC,
CASE+WHEN+@orderColumn+=+2+AND+@orderdir+=+'desc'+THEN+items.[CreatedOn]+END+DESC,
CASE+WHEN+@orderColumn+=+2+AND+@orderdir+=+'asc'+THEN+items.[CreatedOn]+END+ASC

OFFSET+@start+ROWS+FETCH+NEXT+@limit+ROWS+ONLY|code-block|syntax|javascript|1500909|entityMap^0|A|5|G|8|P|9|Z|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|L|$]]

This works fine for me – (<code>where</code>, <code>order by</code>, <code>direction</code>,<code> Pagination</code>)
<pre><code>parameters

@orderColumn int ,
@orderDir varchar(20),
@start int ,
@limit int


select * from items

order by 

CASE WHEN @orderColumn = 0 AND @orderdir = 'desc' THEN items.[CategoryName] END DESC, 
CASE WHEN @orderColumn = 0 AND @orderdir = 'asc' THEN items.[CategoryName] END ASC, 
CASE WHEN @orderColumn = 1 AND @orderdir = 'desc' THEN items.[CategoryValue] END DESC,
CASE WHEN @orderColumn = 1 AND @orderdir = 'asc' THEN items.[CategoryValue] END ASC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'desc' THEN items.[CreatedOn] END DESC,
CASE WHEN @orderColumn = 2 AND @orderdir = 'asc' THEN items.[CreatedOn] END ASC

OFFSET @start ROWS FETCH NEXT @limit ROWS ONLY
</code></pre>

blocks|key|1500970|text|接受的answer的更紧凑的版本，但作为接受的答案，只有当THEN之后的结果表达式具有相同的类型时，才能正常工作。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1500971|ORDER+BY
++++CASE+@OrderDirection+WHEN+0+THEN
++++++++CASE+@sortColumn
+++++++++++WHEN+'AddedDate'+THEN+CONVERT(varchar(50),+AddedDate)
+++++++++++WHEN+'Visible'+THEN+CONVERT(varchar(2),+Visible)
+++++++++++WHEN+'AddedBy'+THEN+AddedBy
+++++++++++WHEN+'Title'+THEN+Title
++++++++END
++++END+ASC,
++++CASE+@OrderDirection+WHEN+1+THEN
++++++++CASE+@sortColumn
+++++++++++WHEN+'AddedDate'+THEN+CONVERT(varchar(50),+AddedDate)
+++++++++++WHEN+'Visible'+THEN+CONVERT(varchar(2),+Visible)
+++++++++++WHEN+'AddedBy'+THEN+AddedBy
+++++++++++WHEN+'Title'+THEN+Title
++++++++END
++++END+DESC|code-block|syntax|javascript|1500972|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/1147806/1548895^0|T|4|3|6|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

More compact version of accepted <a href="https://stackoverflow.com/a/1147806/1548895">answer</a>, but as accepted answer this works fine only when result expressions after <code>THEN</code> have the same type.

<pre><code>ORDER BY
 CASE @OrderDirection WHEN 0 THEN
 CASE @sortColumn
 WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
 WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
 WHEN 'AddedBy' THEN AddedBy
 WHEN 'Title' THEN Title
 END
 END ASC,
 CASE @OrderDirection WHEN 1 THEN
 CASE @sortColumn
 WHEN 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
 WHEN 'Visible' THEN CONVERT(varchar(2), Visible)
 WHEN 'AddedBy' THEN AddedBy
 WHEN 'Title' THEN Title
 END
 END DESC
</code></pre>

blocks|key|1286197|text|以ASC或DESC顺序进行动态排序，与数据类型无关。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1286198|第一个示例按字母顺序排序，第二个示例使用数字排序。@direction变量表示排序方向(0+=+ASC或1=+DESC)，column是排序列。|1286199|这也适用于多列排序，如果将行列放在进一步的外部查询中，则可以隐藏行列。|1286200|DECLARE+@direction+BIT+=+1+--+0+=+ASC+or+1+=+DESC

--+Text+sort.+

SELECT+
++++IIF(@direction+=+0,+ROW_NUMBER()+OVER+(ORDER+BY+[column]+ASC),+ROW_NUMBER()+OVER+(ORDER+BY+[column]+DESC))+[row]
++++,+*
FROM
(+++--+your+dataset.
++++SELECT+N'B'+[column]+
++++UNION+SELECT+N'C'+
++++UNION+SELECT+N'A'
)+[data]+ORDER+BY+[row]+

--+Numeric+sort.

SELECT+
IIF(@direction+=+0,+ROW_NUMBER()+OVER+(ORDER+BY+[column]+ASC),+ROW_NUMBER()+OVER+(ORDER+BY+[column]+DESC))+[row],
*
FROM
(+++--+your+dataset.
++++SELECT+2+[column]+
++++UNION+SELECT+3+
++++UNION+SELECT+1
)+[data]+ORDER+BY+[row]|code-block|syntax|javascript|1286201|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Dynamic sorting in either ASC or DESC order, irrespective of datatype.
The first example sorts alphabetically, the second using numbers. The @direction variable denotes sort direction (0 = ASC or 1 = DESC) and [column] is the sort column.
This also works for multi-column sorting and you can hide the [row] column if placed in a further outer query.
<pre><code>DECLARE @direction BIT = 1 -- 0 = ASC or 1 = DESC

-- Text sort. 

SELECT 
 IIF(@direction = 0, ROW_NUMBER() OVER (ORDER BY [column] ASC), ROW_NUMBER() OVER (ORDER BY [column] DESC)) [row]
 , *
FROM
( -- your dataset.
 SELECT N'B' [column] 
 UNION SELECT N'C' 
 UNION SELECT N'A'
) [data] ORDER BY [row] 

-- Numeric sort.

SELECT 
IIF(@direction = 0, ROW_NUMBER() OVER (ORDER BY [column] ASC), ROW_NUMBER() OVER (ORDER BY [column] DESC)) [row],
*
FROM
( -- your dataset.
 SELECT 2 [column] 
 UNION SELECT 3 
 UNION SELECT 1
) [data] ORDER BY [row]
</code></pre>

I writing a SP that accepts as parameters column to sort and direction.

I don't want to use dynamic SQL.

The problem is with setting the direction parameter.

This is the partial code:

<pre><code>SET @OrderByColumn = 'AddedDate'
SET @OrderDirection = 1;

…

ORDER BY 
 CASE WHEN @OrderByColumn = 'AddedDate' THEN CONVERT(varchar(50), AddedDate)
 WHEN @OrderByColumn = 'Visible' THEN CONVERT(varchar(2), Visible)
 WHEN @OrderByColumn = 'AddedBy' THEN AddedBy
 WHEN @OrderByColumn = 'Title' THEN Title 
 END
</code></pre>

Dynamic order direction

我编写了一个SP，它接受作为参数列的排序和方向。我不想使用动态SQL。问题在于设置方向参数。以下是部分代码：SET @OrderByColumn = 'AddedDate'SET @OrderDirection = 1;…ORDER BY     CASE WHEN @OrderByColumn = 'AddedDat...

问动态排序方向
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问动态排序方向EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问动态排序方向
EN