按列分组并动态排序和排序后在组中选择第一条记录

Question

对于简单而硬编码的场景，它可以工作。

看起来，我在用多列动态排序和排序时，在语法上犯了一些错误。

我得到错误，不正确的语法接近逗号后的Nbr ，我也得到这个错误，不正确的语法接近‘after’

DECLARE @OrderBy varchar(50) = 'ArrivalDate'
DECLARE @OrderDirection smallint = 1

--grouping by Nbr and selecting the first record in the group after sorting and ordering

--Issues with dynamic sorting and ordering
SELECT *
FROM (
       SELECT *, row_number() over (partition by Nbr 
                                      ORDER BY 
                                        CASE WHEN @OrderDirection = 1 THEN
                                           CASE WHEN Lower(@OrderBy) = 'arrivaldate' THEN Nbr, ArrivalDate,
                                           CASE WHEN Lower(@OrderBy) = 'status' THEN Nbr, EnrolmentStatus,
                                           ELSE Nbr
                                        END ASC,                                                                    
                                        CASE WHEN @OrderDirection <> 1 THEN
                                           CASE WHEN Lower(@OrderBy) = 'arrivaldate' THEN Nbr, ArrivalDate,
                                           CASE WHEN Lower(@OrderBy) = 'status' THEN Nbr, EnrolmentStatus                                                                       
                                           ELSE Nbr
                                        END DESC;
                                   ) as row_number
       FROM #TempResults
) as rows
where row_number = 1

我很快创建了一个脚本来播放一些数据。

CREATE TABLE #TempResults
(
  Nbr int,  
  StudentName varchar(50) null, 
  EnrolmentStatus char(1), 
  ArrivalDate datetime, 
  TransportMode char(1) 
)

SELECT * FROM #TempResults
INSERT INTO #TempResults (Nbr,  StudentName, EnrolmentStatus, ArrivalDate,TransportMode)
VALUES 
(1, 'ABC-1', 'A', '2021-04-12', 'X'),
(2, 'ABC-1', 'B', '2021-04-12', 'X'),
(3, 'ABC-1', 'A', '2021-04-11', 'Y'),
(4, 'ABC-1', 'A', '2021-04-11', 'Z'),
(5, 'ABC-1', 'A', '2021-04-10', 'X'),
(6, 'ABC-1', 'B', '2021-04-11', 'X'),
(7, 'ABC-1', 'A', '2021-04-11', 'Y'),
(8, 'ABC-1', 'A', '2021-04-08', 'Z'),
(1, 'ABC-2', 'A', '2021-04-12', 'X'),
(2, 'ABC-2', 'B', '2021-04-12', 'X'),
(3, 'ABC-2', 'A', '2021-04-11', 'Y'),
(4, 'ABC-2', 'A', '2021-04-11', 'Z'),
(5, 'ABC-2', 'A', '2021-04-10', 'X'),
(6, 'ABC-2', 'B', '2021-04-11', 'X'),
(7, 'ABC-2', 'A', '2021-04-11', 'Y'),
(8, 'ABC-2', 'A', '2021-04-08', 'Z'),
(1, 'ABC-3', 'A', '2021-04-12', 'X'),
(2, 'ABC-3', 'B', '2021-04-12', 'X'),
(3, 'ABC-3', 'A', '2021-04-11', 'Y'),
(4, 'ABC-3', 'A', '2021-04-11', 'Z'),
(5, 'ABC-3', 'A', '2021-04-10', 'X'),
(6, 'ABC-3', 'B', '2021-04-11', 'X'),
(7, 'ABC-3', 'A', '2021-04-11', 'Y'),
(8, 'ABC-3', 'A', '2021-04-08', 'Z'),
(1, 'ABC-3', 'A', '2021-04-12', 'X'),
(2, 'ABC-3', 'B', '2021-04-12', 'X'),
(3, 'ABC-3', 'A', '2021-04-11', 'Y'),
(4, 'ABC-3', 'A', '2021-04-11', 'Z'),
(5, 'ABC-3', 'A', '2021-04-10', 'X'),
(6, 'ABC-3', 'B', '2021-04-11', 'X'),
(7, 'ABC-3', 'A', '2021-04-11', 'Y'),
(8, 'ABC-3', 'A', '2021-03-08', 'Z'),
(4, 'ABC-5', 'A', '2021-04-11', 'Z'),
(5, 'ABC-5', 'A', '2021-03-10', 'X'),
(6, 'ABC-5', 'B', '2021-03-11', 'X'),
(7, 'ABC-5', 'A', '2021-03-11', 'Y'),
(8, 'ABC-5', 'A', '2021-03-08', 'Z'),
(1, 'ABC-5', 'A', '2021-04-12', 'X'),
(2, 'ABC-5', 'B', '2021-04-12', 'X'),
(3, 'ABC-5', 'A', '2021-03-11', 'Y'),
(4, 'ABC-5', 'A', '2021-03-11', 'Z'),
(6, 'ABC-6', 'B', '2021-03-11', 'X'),
(7, 'ABC-6', 'A', '2021-03-11', 'Y'),
(8, 'ABC-6', 'A', '2021-03-08', 'Z'),
(1, 'ABC-6', 'A', '2021-03-12', 'X'),
(2, 'ABC-6', 'B', '2021-04-01', 'X'),
(3, 'ABC-6', 'A', '2021-04-02', 'Y'),
(4, 'ABC-6', 'A', '2021-04-03', 'Z'),
(5, 'ABC-6', 'A', '2021-04-07', 'X')

Answer 1

查询中注意到的几个问题

您的案例表达式语法是错误的。这里和那里都缺少END和额外的逗号。请参阅有关案例的文档以获得正确的语法。

当input_expression when_expression和result_expression ...n结束时

CASE是表达式，而不是语句。表达式返回单个值。您的CASE中有多个列

另一点是，CASE表达式的所有返回路径必须具有相同的数据类型。你有ArrivalDate - datetime和EnrolmentStatus - varchar.下面的查询，我已经将datetime转换为varchar

您的row_number()查询应该是

 row_number() over 
 (
     PARTITION BY Nbr 
     ORDER BY 
         CASE WHEN @OrderDirection = 1 THEN
              CASE WHEN Lower(@OrderBy) = 'arrivaldate' THEN convert(varchar(10), ArrivalDate, 121)
                   WHEN Lower(@OrderBy) = 'status'      THEN EnrolmentStatus
              END 
         END ASC,
         CASE WHEN @OrderDirection <> 1 THEN
              CASE WHEN Lower(@OrderBy) = 'arrivaldate' THEN convert(varchar(10), ArrivalDate, 121)
                   WHEN Lower(@OrderBy) = 'status'      THEN EnrolmentStatus 
              END 
         END DESC
 ) as row_no

我将别名更改为row_no，而不是row_number，后者是SQL Server中的关键字。

Answer 2

处理“动态”ORDER BY子句很棘手。问题是，CASE是一个只返回单个值的表达式--并且类型是在编译期间确定的。

我建议为每个条件编写一个单独的CASE表达式：

ORDER BY nbr,
         ROW_NUMBER() OVER (PARTITION BY Nbr 
                            ORDER BY (CASE WHEN @OrderDirection = 1 AND Lower(@OrderBy) = 'arrivaldate' THEN ArrivalDate END),
                                     (CASE WHEN @OrderDirection = 1 AND Lower(@OrderBy) = 'status' THEN EnrolmentStatus END),
                                     (CASE WHEN @OrderDirection <> 1 AND Lower(@OrderBy) = 'arrivaldate' THEN ArrivalDate END) DESC,
                                     (CASE WHEN @OrderDirection <> 1 AND Lower(@OrderBy) = 'status' THEN EnrolmentStatus END) DESC
                           ) as row_number

这有点冗长，但您不必担心不同列类型之间的类型转换。