alpha 列的条件 SQL ORDER BY ASC/DESC

发布于 2024-09-27 00:52:46 字数 621 浏览 0 评论 0原文

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

我希望排序方法（ASC 或 DESC）是有条件的。

现在，对于数字列，我只需使用 case 语句并否定该值以模拟 ASC 或 DESC... 也就是说：

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

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

编辑：我想到了一个聪明的方法，但它似乎效率非常低...我可以将我的有序 alpha 列插入到带有自动编号的临时表中，然后使用上述方法按自动编号进行排序。

EDIT2：

你们觉得这种方法怎么样？

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

我不知道在统一列上强制排序是否比从排序字符串中派生数字更有效

原文

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

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

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

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

EDIT: 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Spring初心 2024-10-04 00:52:46

一个选项

;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

或 CASE 恕我直言有点难看

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

One option

;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

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

回复收藏 0 原文

高速公鹿 2024-10-04 00:52:46

这是特定解决方案可能比通用解决方案更可取的情况之一，特别是当我们处理大量数据时。我会：

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] DESC
END

如果您在 [AlphaColumn] 上有索引，有时您可能会通过更具体的查询获得比通用的一刀切查询更好的计划。

编辑：为了促进代码重用，您可以将选择包装在内联 UDF 中 - 它的性能也一样：

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  YourInlineUdf(...)
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  YourInlineUdf(...) 
  ORDER BY [AlphaColumn] DESC
END

This is one of those cases when specific solutions may be preferable preferable to generic ones, especially when we deal with large amounts of data. I would:

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  ... 
  ORDER BY [AlphaColumn] DESC
END

If you have an index on [AlphaColumn], you might sometimes get a better plan with a more specific query, than with a generic one-size-fits-all one.

Edit: to facilitate code reuse, you can wrap your select in an inline UDF - it will perform just as well:

IF @OrderAscOrDesc = 0 THEN BEGIN
  SELECT ...
  FROM  YourInlineUdf(...)
  ORDER BY [AlphaColumn] ASC
END ELSE BEGIN
  SELECT ...
  FROM  YourInlineUdf(...) 
  ORDER BY [AlphaColumn] DESC
END

回复收藏 0 原文

~没有更多了~