数据库复习

一.SQL Server查询第31到40条数据？

大致分为两种情况：ID连续和ID不连续。

1.ID连续的情况：select * from A where ID between 31 and 40
2.ID不连续的情况：

(1)两次对表查询，效率较低。
　　select top 10 * from A where ID not in (select top 30 ID from A)
(2)外层查询没有对表A进行查询，效率提高。
　　select top 10 * from (select top 40 ID from A order by ID) as a order by a.ID desc
(3)ROW_NUMBER()函数效率更高,SQL2005以上版本可用。
　　select * from(select *,ROW_NUMBER() over(order by ID)as ‘userID‘ from A) as a where a.userID between 31 and 40

数据库复习

标签：and   over   use   sel   between   serve   查询   order   版本