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