通过分页SQL查询进行动态排序

问题描述

我想进行分页&存储过程中的动态orderby子句(超过一个列).我确实尝试了以下操作，但给了我一个错误

I would like to have paging & dynamic orderby clause in a stored procedure (THAT TOO ON MORE THAN ONE COLUMN). I did try following but gives me an error

似乎我不能在rank()上使用Row_number()

It seems I cannot use Row_number() over rank()

除了linq到sql之外，还有其他方法可以实现这一目标

Is there any alternative to achieve this one, apart from linq to sql

SELECT  [t8].[AssetId],
            [t8].[WorkOrderId],
            [t8].[IssueDescription] AS [WorkOrderDescription],
            [t8].[value] AS [Type],
            [t8].[WorkOrderStatusTypeName] AS [Status],
            [t8].[value2] AS [StartDate],
            [t8].[CompletedDate] AS [CompleteDate],
            [t8].[value22] AS [CompletedBy],
            ISNULL([t8].[value3],0) AS [Hours]
    FROM    (
                SELECT  ROW_NUMBER()
                    OVER (ORDER BY  CASE WHEN @sortColumnName = 'default' THEN (RANK() over( order by [t7].[WorkOrderStatusTypeId] ASC, [t7].[WorkOrderId])) END ,
                                    CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
                                    CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
                    ) AS [ROW_NUMBER],
                    [t7].[AssetId],
                    [t7].[WorkOrderId],
                    [t7].[IssueDescription],
                    [t7].[value],
                    [t7].[WorkOrderStatusTypeName],
                    [t7].[value2],
                    [t7].[CompletedDate],
                    [t7].[value22],
                    [t7].[value3]
            from    --Different tables
            ) as t8
    WHERE       [t8].[ROW_NUMBER] BETWEEN ((@pageIndex-1) * @pageSize)+ 1 AND @pageIndex * @pageSize
    ORDER BY    [t8].[ROW_NUMBER]

推荐答案

已使用

SELECT  CASE
        WHEN @sortColumnName ='default' AND @sortOrder = 'asc'  then row_number() over (order by [t7].[WorkOrderStatusTypeId], [t7].[CompletedDate] ASC)
        WHEN @sortColumnName ='WorkOrderId' AND @sortOrder = 'asc'  then row_number() over (order by [t7].[WorkOrderId] ASC)
        WHEN @sortColumnName ='WorkOrderId' AND @sortOrder = 'desc' then row_number() over (order by [t7].[WorkOrderId] DESC)
        END AS [ROW_NUMBER],

代替

SELECT  ROW_NUMBER()
        OVER (ORDER BY  CASE WHEN @sortColumnName = 'default' THEN (RANK() over( order by [t7].[WorkOrderStatusTypeId] ASC, [t7].[WorkOrderId])) END ,
                        CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'asc' THEN [t7].[WorkOrderId] END ASC,
                        CASE WHEN @sortColumnName = 'WorkOrderId' AND @sortOrder = 'desc' THEN [t7].[WorkOrderId] END DESC
        ) AS [ROW_NUMBER],

不能将嵌套的窗口函数(如RowNumber()和Rank())一起使用，而是可以在case语句中使用.

Cannot have nested windowed functions like RowNumber() and Rank() together, instead they can be used inside case statement.

这篇关于通过分页SQL查询进行动态排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！