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 [temp