SQL Select N to M Records (single Table)

取表里n到m条纪录的几种方法:

 

1. 只需要查询前M条数据(0 to M),

1.1 使用 top(M) 方法:

select top(3) * from [tablename]

 

1.2 使用 set rowcount 方法:
http://msdn.microsoft.com/zh-cn/library/ms188774(SQL.90).aspx

set rowcount M
select * from [tablename]
set rowcount 0

权限 要求具有 public 角色成员资格。
要执行set rowcount 0, 否则影响以后查询等.

 

 

2.查询N到M条数据(N to M),

2.1  表里面有标识列

2.1.1

select top (M-N+1) * from [tablename] where [columnname] not in (select top (N) [columnname] from [tablename])

 

2.1.2  逆序显示

select top N * from (select top M * from [tablename] order by [columnname]) temp order by [columnname] desc

 

2.1.3 顺序显示

select * from (select top N * from (select top M * from [tablename] order by [columnname]) temp1 order by [columnname] desc) temp2 order by [columnname]

 

 

2.2 表里有identity属性

select * from [tablename] where identitycol between N and M

 

如[columnname]为identity属性,则可以写成:
select * from [tablename] where [columnname] between N and M

 

2.3 表里面有标识列, 利用临时表

IF Exists(Select 1 From sysObjects Where Name ='temptable' And Type In ('temptable','U'))
begin
    drop table [temptable]
end
select top M * into [temptable] from [tablename] order by [columnname]
set rowcount N
select * from [temptable] order by [columnname] desc
set rowcount 0
drop table [temptable] 

 

2.4 如果tablename里没有其他identity列，那么：

exec sp_dboption [DataBaseName] ,'select into/bulkcopy',true
IF Exists(Select 1 From sysObjects Where Name ='temptable' And Type In ('temptable','U'))
begin
    drop table temptable
end
select identity(int) id0,* into [temptable] from [tablename]
select * from temp where id0 >= N and id0 <= M
drop table temptable 

 

如果你在执行select identity(int) id0,* into [temptable] from [tablename] 这条语句的时候报错,那是因为你的DB中的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,'select into/bulkcopy',true