Alpha列的条件SQL ORDER BY ASC / DESC [英] Conditional SQL ORDER BY ASC/DESC for alpha columns

问题描述

在MS SQL Server 2008 R2中编写存储过程，我想避免使用DSQL ...

Writing a stored procedure in MS SQL Server 2008 R2, I want to avoid using DSQL...

我希望将排序方法（ASC或DESC）用于

I would like the sort method (ASC or DESC) to be conditional.

现在，使用数字列，我只需要使用一个case语句并取反值来模拟ASC或DESC ...
即：

Now, with a numeric column I would simply use a case statement and negate the value to emulate ASC or DESC... That is:

... ORDER BY CASE @OrderAscOrDesc WHEN 0 THEN [NumericColumn] ELSE -[NumericColumn] END ASC

使用alpha列执行此操作的合适方法是什么？

What is an appropriate method for doing this with an alpha column?

编辑：我想到了一种巧妙的方法，但是效率似乎很低……我可以使用自动编号将有序的alpha列插入到临时表中，然后使用上述方法按自动编号排序。

I thought of a clever way but it seems terribly inefficient... I could insert my ordered alpha column into a temp table with an autonumber then sort by the autonumber using the method described above.

EDIT2：

你们如何看待这种方法？

What do you guys think of this approach?

ORDER BY CASE @OrderAscOrDesc WHEN 0 THEN [AlphaColumn] ELSE '' END ASC,
CASE @OrderAscOrDesc WHEN 0 THEN '' ELSE [AlphaColumn] END DESC

我不知道在统一列上强制排序是否比从中推导数字更有效尽管已排序字符串

I don't know if forcing a sort on a uniform column is more efficient than deriving numbers from sorted strings though

推荐答案

一个选项

;WITH cQuery AS
(
   SELECT
       *,
       ROW_NUMBER() OVER (ORDER BY SortColumn) AS RowNum
   FROM
       MyTable
)
SELECT
   *
FROM
   cQuery
ORDER BY
   RowNum * @Direction --1 = ASC or -1 = DESC

或者IMHO有点丑的情况

Or CASE which IMHO is a bit uglier

ORDER BY
  CASE WHEN 'ASC' THEN SortColumn ELSE '' END ASC,
  CASE WHEN 'DESC' THEN SortColumn ELSE '' END DESC

这篇关于Alpha列的条件SQL ORDER BY ASC / DESC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！