通过分页SQL查询进行动态排序 [英] Dynamic order by in Paging SQL Query

问题描述

我想进行分页&存储过程中的动态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()

窗口函数不能在另一个窗口函数或聚合的上下文中使用.

Windowed functions cannot be used in the context of another windowed function or aggregate.

除了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查询进行动态排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！