如何在ROW_NUMBER() OVER (ORDER BY子句中对日期时间进行排序

Question

如何在ROW_NUMBER() OVER (ORDER BY子句中对日期时间进行排序

sqlsql-server-2005

4

你好，下面的查询是一个更复杂查询的原型。问题在于我应该能够根据用户输入按任意列以任何顺序排序（即升序和降序）。

CREATE table #Table1(
Name varchar(10) PRIMARY key,
DOB DateTime,
Rate numeric(10,2)  
)

INSERT INTO #Table1 (Name,DOB,Rate) values ('Name1','2/2/2012',10.23)
INSERT INTO #Table1 (Name,DOB,Rate) values ('Name2','3/2/2012',120.23)
INSERT INTO #Table1 (Name,DOB,Rate) values ('Name3','4/2/2012',110.23)
INSERT INTO #Table1 (Name,DOB,Rate) values ('Name4','5/2/2012',140.23)
INSERT INTO #Table1 (Name,DOB,Rate) values ('Name15','6/2/2012',130.23)
INSERT INTO #Table1 (Name,DOB,Rate) values ('Name6','2/21/2012',1120.23)

Declare @SortColumn varchar(10)
DECLARE @SortExpression varchar (10)

SET @SortColumn = 'DOB'
SET @SortExpression = 'DESC' -- Need to sort in both ASC and DESC

Select 
            Name,
            DOB,
            Rate,
            ROW_NUMBER() OVER 
                (ORDER BY 
                    CASE WHEN @SortColumn = 'Name' then Name
                         WHEN @SortColumn = 'DOB' THEN DOB
                         WHEN @SortColumn = 'Rate' THEN Rate
                    END  +  @SortExpression 
                ) AS RowNumber  
                FROM 
                #Table1

- firdouse

请查看此处的答案：https://dev59.com/H0fRa4cB1Zd3GeqP7Tkx#1479623。然而，分开查询可能会有更好的计划。顺便说一下，`ASC`和`DESC`不能作为您尝试的参数化，并且`CASE`的所有分支都需要转换为相同的数据类型。 - Martin Smith

2个回答

0

如果您需要动态地在 ASC 和 DESC 之间切换，您将需要使用动态语句。
SQL Fiddle 这里。请注意SQL注入！

EXECUTE(' SELECT Name, DOB, Rate,'
      + ' ROW_NUMBER() OVER( ORDER BY ' + @SortColumn + ' ' + @SortExpression
      + ' ) AS RowNumber'
      + ' FROM Table1');

如果您的查询比较复杂，考虑将它放入一个视图中，并从该视图中进行选择，以允许在编译时检查该部分。

- Peter Lang

尽管在视图中无法使用动态 SQL，但可以使用存储过程。 - Taryn

抱歉，我怕在视图中无法放置“ASC”/“DESC”。 - Peter Lang

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Taryn · Accepted Answer

看起来您需要在CASE语句中使用CAST()和CONVERT()转换数据类型才能使其正常工作：

Declare @SortColumn varchar(10)
DECLARE @SortExpression varchar (10)

SET @SortColumn = 'DOB'
SET @SortExpression = 'DESC' -- Need to sort in both ASC and DESC

Select 
   Name,
   DOB,
   Rate,
   ROW_NUMBER() OVER 
   (ORDER BY 
       CASE WHEN @SortColumn = 'Name' then Name
            WHEN @SortColumn = 'DOB' THEN convert(char(10), DOB, 120)
            WHEN @SortColumn = 'Rate' THEN Cast(Rate as varchar(10))
       END  ASC
    ) AS RowNumberASC,
    ROW_NUMBER() OVER 
    (ORDER BY 
       CASE WHEN @SortColumn = 'Name' then Name
            WHEN @SortColumn = 'DOB' THEN convert(char(10), DOB, 120)
            WHEN @SortColumn = 'Rate' THEN Cast(Rate as varchar(10))
      END DESC
    ) AS RowNumberDESC
FROM Table1

请查看带演示的SQL Fiddle

@Martin指出ASC和DESC无法进行参数化。