动态排序方向
动态排序方向
提问于 2009-07-18 15:10:01
我编写了一个SP，它接受作为参数列的排序和方向。
我不想使用动态SQL。
问题在于设置方向参数。
以下是部分代码：
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回答 5
Stack Overflow用户
回答已采纳
发布于 2009-07-18 15:27:20
您可以拥有两个几乎相同的ORDER BY项，一个ASC和一个DESC，并扩展CASE语句，使它们中的一个始终等于单个值：
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 DESCStack Overflow用户
发布于 2009-07-18 17:19:58
您可以使用ROW_NUMBER来简化这种情况，它可以对数据进行排序，并有效地将其转换为方便的整数格式。尤其是因为问题被标记为SQL Server2005
它还可以轻松地进行扩展，以处理第二和第三排序
我使用multiplier再次简化了实际的select语句，并减少了ORDER BY中RBAR求值的机会
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;Stack Overflow用户
发布于 2015-12-30 16:37:17
这对我来说很好- (where，order by，direction，Pagination)
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1147763
复制相关文章
相似问题