动态订单方向

Question

我编写了一个 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

Answer 1

您可以有两个几乎相同的 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 DESC

Answer 2

您可以使用 ROW_NUMBER 来简化 CASE，它对数据进行排序并有效地将其转换为方便的整数格式。 特别是因为问题被标记为 SQL Server 2005

这也可以轻松扩展以处理二级和三级排序，

我使用乘法器再次简化实际的 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;

Answer 3

这对我来说效果很好 - (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

Answer 4

这是一个示例：

CREATE PROCEDURE GetProducts 
( 
    @OrderBy      VARCHAR(50), 
    @Input2       VARCHAR(30) 
) 
AS 
BEGIN 
    SET NOCOUNT ON 

    SELECT Id, ProductName, Description, Price, Quantity 
    FROM Products 
    WHERE ProductName LIKE @Input2 
    ORDER BY 
        CASE             
            WHEN @OrderBy = 'ProductNameAsc' THEN ProductName 
        END ASC, 
        CASE 
            WHEN @OrderBy = 'ProductNameDesc' THEN ProductName 
        END DESC 

END

从这里：

http://www.domincpettifer.co.uk/Blog/21/dynamic-conditional-order-by-clause-in-sql-server-t-sql

需要升序和降序操作
被分组到单独的 CASE 中
语句，用逗号分隔。 在
您的服务器端代码/脚本确保
将“Asc”或“Desc”附加到
按字符串排序，或者你可以有两个
存储过程输入参数
列名称和按方向排序 if
你想要的。

Answer 5

已接受的答案的更紧凑版本，但作为已接受的答案，仅当THEN 具有相同的类型。

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

Answer 6

无论数据类型如何，都按 ASC 或 DESC 顺序动态排序。

第一个示例按字母顺序排序，第二个示例使用数字排序。 @direction 变量表示排序方向（0 = ASC 或 1 = DESC），[column] 是排序列。

这也适用于多列排序，如果放置在进一步的外部查询中，您可以隐藏 [row] 列。

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]