在情况下使用订单和方向，但通过辅助列进行错误排序

发布于 2025-02-13 02:11:40 字数 750 浏览 0 评论 0原文

下面的工作正常：

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC 
 ...

我想做的是在每种情况下都应用次级排序订单，而不论方向如何：

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, announce.ID Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC, announce.ID Desc,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, announce.Date Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, announce.Date Desc

但是上面给我错误：

按列表按顺序指定了一个列。按列表顺序排列的列必须是唯一的。

原文

Below works fine:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC 
 ...

What I want to do is apply a secondary sort order in each case which is same regardless of direction:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, announce.ID Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC, announce.ID Desc,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, announce.Date Desc,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, announce.Date Desc

But above gives me error:

A column has been specified more than once in order by list. Columns in order by list must be unique.

分享到QQ

分享到微博

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

发布评论

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

纵情客 2025-02-20 02:11:40

如果两种情况下，则无需重复两个方向的排序标准，而且它们也不必在其他条件旁边。前四个案例表达式中只有一个可以返回true，也只能返回最后两个：

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC,
 CASE WHEN @SortBy = 1 THEN announce.ID END DESC,
 CASE WHEN @SortBy = 2 THEN announce.Date END DESC;

You don't need to repeat the sort criteria for both directions if they're the same in either case, and they don't have to be right next to the other criteria either. Only one of the first four CASE expressions can possibly return true, and also only of the last two:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC, 
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC,
 CASE WHEN @SortBy = 1 THEN announce.ID END DESC,
 CASE WHEN @SortBy = 2 THEN announce.Date END DESC;

回复收藏 0 原文

〗斷ホ乔殘χμё〖 2025-02-20 02:11:40

给定@sortdirection = 1和@sortby = 1您的类别将转化为类似的东西：

--Sort 1
ORDER BY
 announce.[Status] ASC,
 NULL DESC
...
--Sort 2
ORDER BY
 announce.[Status] ASC, announce.ID Desc,
 NULL DESC, announce.ID Desc,
 NULL ASC, announce.Date Desc,
 NULL DESC, announce.Date Desc

也许尝试这样的东西：

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, 
announce.ID Desc,
announce.Date Desc

Given @SortDirection = 1 and @SortBy = 1 your sorts will translate to something like this:

--Sort 1
ORDER BY
 announce.[Status] ASC,
 NULL DESC
...
--Sort 2
ORDER BY
 announce.[Status] ASC, announce.ID Desc,
 NULL DESC, announce.ID Desc,
 NULL ASC, announce.Date Desc,
 NULL DESC, announce.Date Desc

Maybe try something like this:

ORDER BY
 CASE WHEN @SortDirection = 1 AND @SortBy = 1 THEN announce.[Status] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 1 THEN announce.[Status] END DESC,
 CASE WHEN @SortDirection = 1 AND @SortBy = 2 THEN announce.[Title] END ASC,
 CASE WHEN @SortDirection = 2 AND @SortBy = 2 THEN announce.[Title] END DESC, 
announce.ID Desc,
announce.Date Desc

回复收藏 0 原文

~没有更多了~