查询数据库中满足条件的特定行数据，在这里主要给出三条查询语句，其中第三条主要是针对SQL Server2005数据库的，因为其中的Row_Number()函数只有在SQL Server2005中才支持。
例子：
我数据库中有一个table表，表中一共有50条数据，我现在要查询第21到30条数据，我可以对这50条数据分成5页，每页10条数据。
一、select top 页大小 * from table1 where (id not in (select top (页大小-1)*每页数 id from 表 order by id))order by id
例子：select top 10 * from table where (id not in (select top 20 id from table order by id))order by id
二、select top 页大小 * from table1 where id>(select max (id) from (select top ((页码-1)*页大小) id from table1 order by id) as t) order by id
例子：select top 10 * from table where id>(select max (id) from (select top 20 id from table order by id) as t) order by id
总结：二比一好，not in费时
三、select * from(select ROW_NUMBER() over(order by id) -1 as rownum,table * from
依据什么排序 默认行号为-1+1=0 table) as d where rownum between 0 and 10 起始行 显示多少行
例子：select * from(select ROW_NUMBER() over(order by ID desc) as rownum,table * from table) as d where rownum between 21 and 30
此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。 原文来自 — 版权归原作者所有。