原来没有遇上,倒是没认真考虑过这个问题。Sql Server适用
Record1
Record2
...
...
RecordN
...
...
RecordN+M
...
...
Result1:select top N+M from tableName order by Field1;/*Field1的顺序是表的顺序*/
Record1
Record2
...
...
RecordN
...
...
RecordN+M
Result2:select top M from Result1 order by Field1 desc;
RecordN+M
RecordN+M-1
...
...
RecordN
Result3:select * from Result2 order by Field2;
最后的语句是:select * from (select top M from (select top N+M from tableName order by Field1) order by Field1 desc) order by Field1;
2004-7-30补(下面Oracle 中适用):
这样做有一个问题,如果M是一个已经order by的语句,外层的order by 也有可能会破坏内层的order by ,所以需要加一层约束,Field1必须是内层order by 的第一个字段,经过简单测试,这是可以的,测试案例在后面。
另外一种获取N到N+M条记录的方法是:
SQL> select * from (select rownum as xfdgdf,tableName.* from tableName OriTable) where xfdgdf between N and N+M;
上面比正常需要的多出了一个字段xfdgdf,却是如何去掉呢??
SQL> select custid,custname from customer;
CUSTID CUSTNAME
---------- --------------------
35 444
35 6666
445 676
35 aaa
35 5555
SQL> select custid,custname from customer order by custid,custname;
CUSTID CUSTNAME
---------- --------------------
35 444
35 5555
35 6666
35 aaa
445 676
SQL> select * from (select * from (select custid,custname from customer order by
custid,custname) order by custid desc) order by custid;
CUSTID CUSTNAME
---------- --------------------
35 444
35 5555
35 6666
35 aaa
445 676
7月31日补:
注:select * from tablename where rownum>10并不能如愿取出rownum在rownum后面的纪录
再到今天又发现另外一个的方法,不过没有测试,不知道效率比上面的方法效率有没有高一点,另外也没有解决多一个字段的问题。来自
http://blog.codelphi.com/progame/archive/2004/02/21/1456.aspx
Select rownum,* from MyTable where rownum<10
minus
Select rownum,* from MyTable where rownum<5
这个方法比上面那个方法应该比较通用,在SQL server里也许能用类似的方法实现
Select top 10 * from MyTable
minus
Select top 5 * from MyTable