利用 ROW_NUMBER() OVER ( ORDER BY 进行选择性排序，按不同字段进行排序处理，分页...

--就在OVER order by 中用case语句进行判断。

 IF ( OBJECT_ID('tempdb..#TempTable') IS NOT NULL ) 
        DROP TABLE #TempTable

    SELECT  *
    INTO    #TempTable
    FROM    ( SELECT    ROW_NUMBER() 
    OVER ( ORDER BY 
    CASE WHEN @orderby ='CreateDate DESC' THEN CreateDate END DESC,
    CASE WHEN @orderby ='CreateDate ASC' THEN CreateDate END ASC,
    CASE WHEN @orderby ='Sort DESC' THEN SortEND DESC,
    CASE WHEN @orderby ='Sort ASC' THEN SortEND ASC,
    CASE WHEN @orderby ='Status DESC' THEN Status END DESC,
    CASE WHEN @orderby ='Status ASC' THEN Status END ASC  ) rownum ,
                        *
              FROM      Table  AS  t) temp
IF ( @pageIndex = 0
AND @pageSize = 0
) 
SELECT * 
FROM #TempTable AS t
ELSE 
SELECT *
FROM #TempTable AS t
WHERE rownum > ( @pageIndex - 1 ) * @pageSize
AND rownum <= @pageIndex * @pageSize
SELECT @RecordCount = COUNT(1)
FROM #TempTable 

DROP TABLE #TempTable

　　

  当然 @orderby 也可定义 int 类型的。用数字代替，判断，效率方面应该好些。用字符说明，更容易直观阅读。

  数据查询出来，在进行分页处理操作。

转载于:https://www.cnblogs.com/ksport/p/3497714.html