【信息】问题：如何用SQL文将表B按字段X升序,字段Y降序排序后，取出第n到第m个记录？[supfrie] [复制链接]

操作系统：Windows2000(Server & Professional)
编程工具：SQL Server 7.0
问题：如何用SQL文将表B按字段X升序,字段Y降序排序后，取出第n到第m个记录？

回答:

select top m-n col1, col2...
from table b
where col1 not in
(select top n from table b order by col1 (asc, desc)
col2 (asc, desc) ...)
order by col1 (asc, desc), col2 ...

For example:

need records from 15 to 20 in pubs..authors table
order by au_id desc, au_lname asc:

select top 5 au_id, au_lname, au_fname
from authors
where au_id not in
(select top 15 au_id from authors order by au_id desc, au_lname asc)
order by au_id desc, au_lname asc

Guest的意见：
1.NOT IN的效率很低
2.用存储过程实现，主要是利用滚动光标
(declare scroll cursor...)

阿甘的意见：
select * from (select top m-n * from (select top 100 percent * from (select top m * from (select top 100 percent * from B order by X,Y desc) a1) a2 order by Y,X desc) a3) a4 order by X ,Y desc