sql²éѯÎÊÌâ - MS-SQL Server / »ù´¡Àà

Ò»¸öÊý¾ÝÊý¾ÝÌرð¶à£¬Èç¹ûÎÒÏë²éÊý¾ÝµÄµÚ0-1000Ìõ£¬1000-2000£¬2000-3000 ÕâÑùµÄ¸ÃÔõôдsqlÓï¾äÄØ

---

²Î¿¼
SQL code:
È¡nµ½mÐÐ

1.
select top m * from tablename where id not in (select top n id from tablename order by id asc/*|desc*/)

2.
select top m * into ÁÙʱ±í(»ò±í±äÁ¿) from tablename order by columnname -- ½«top m±Ê²åÈëµ½ÁÙʱ±í
set rowcount n --ֻȡnÌõ½á¹û
select * from ±í±äÁ¿ order by columnname desc

3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc


4.Èç¹ûtablenameÀïûÓÐÆäËûidentityÁУ¬ÄÇô£º
ÏÈÉú³ÉÒ»¸öÐòÁÐ,´æ´¢ÔÚÒ»ÁÙʱ±íÖÐ.
select identity(int) id0,* into #temp from tablename

È¡nµ½mÌõµÄÓï¾äΪ£º
select * from #temp where id0 > =n and id0 <= m

Èç¹ûÄãÔÚÖ´ÐÐselect identity(int) id0,* into #temp from tablenameÕâÌõÓï¾äµÄʱºò±¨´í,ÄÇÊÇÒòΪÄãµÄDBÖмäµÄselect into/bulkcopyÊôÐÔûÓдò¿ªÒªÏÈÖ´ÐУº
exec sp_dboption ÄãµÄDBÃû×Ö,'select into/bulkcopy',true


5.Èç¹û±íÀïÓÐidentityÊôÐÔ£¬ÄÇô¼òµ¥£º
select * from tablename where identity_col between n and m

6.SQL2005¿ªÊ¼.¿ÉÒÔʹÓÃrow_number() over()Éú³ÉÐкÅ
;with cte as
(
select id0=row_number() over(order by id),* from tablename
)
select * from cte where id0 between n to m

---

ÒýÓÃ

²Î¿¼

SQL code
È¡nµ½mÐÐ

1.
select top m