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

在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;
GO
WITH 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;