sqlserver2008中在实现limit功能的同时实现order by 排序功能

来源：互联网发布：淘宝买家怎么提升信誉编辑：程序博客网时间：2024/05/21 06:59

在sqlserver中，以前在CTE查询中无法进行order by 操作，或者在子查询中也无法实现order by 操作。诸如：

/*以下两条语句都是无法执行的*/

WITH StudentListCTE AS(            SELECT top *     FROM Student    WHERE schoolId = @schoolId    ORDER BY pinyin ASC  )SELECT *FROM StudentListCTE;SELECT *FROM (    SELECT *     FROM Student    WHERE schoolId = @schoolId    ORDER BY pinyin ASC      )AS TableStudent

因此要想实现以上的两种查询方式中进行order by必须指定top + number操作，但有时候我们需要查询所有的记录，无法确定number值。但我们可以通过以下方式进行实现

WITH StudentListCTE AS(            SELECT top 100 percent *     FROM Student    WHERE schoolId = @schoolId    ORDER BY pinyin ASC  )SELECT *FROM StudentListCTE;SELECT *FROM (    SELECT  top 100 percent *     FROM Student    WHERE schoolId = @schoolId    ORDER BY pinyin ASC      )AS TableStudent

同时sqlserver 可以通过row_number()函数结合top 100 percent实现类似mysql的limit功能并进行排序操作

USE AdventureWorks;GOWITH OrderedOrders AS(    SELECT TOP 100 percent SalesOrderID, OrderDate,    ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS 'RowNumber'    FROM Sales.SalesOrderHeader        where orderdate >= '2013-04-01'   ORDER BY orderDate DESC ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 10 AND 20;