选择特定范围内的记录

数据库：twt001

数据表：asample

 

(1)SQL查找第n条记录的方法:
select top 1 * from table where id not in (select top n-1 id from table)

 选取第1条记录：

select top 1 * from asample where xh not in (select top 0 xh from asample) 

选取第6条记录：

select top 1 * from asample where xh not in (select top 5 xh from asample) 

 

（2）SQL查找第n条开始的m条记录的方法:
select top m * from table where id not in (select top n-1 id from table)

选取从第10条=11(n)-1开始的5(m)条记录

select top 5 * from asample where xh not in (select top 9 xh from asample) 

 

(注：表中必须有一个唯一值字段才可适用此方法。) 

 (3)

数据库：Northwind

select  top 70 percent firstname as '名字' , lastname as '姓氏'
 from employees 
 where firstname like '_[t,a]%' /*检索出符合条件的前2条记录*/
 select  top 2 firstname as '名字' , lastname as '姓氏'
 from employees 
 where firstname like '_[t,a]%'

运行如下：