sql server选取第m行到第n行的元组

现在有一个表Questioin,主码是qid,要求选择第m行到第n行的元组

//方法一:效率最低

//错误:如果n<m,将选取前n条数据,如果n>=m,将选取从m+1开始的n条数据

select top n *
from Question
where qid not in
{
select top m qid
from Question
}

//当然改成下面的就正确了

select top(n-m)*
from Question
where qid not in
(select top m id from Question)

//方法二:先排序,据说效率最高

select top m * from
(select top n * from Question order by qid asc)a
order by qid desc

//方法三:利用ROW_NUMBER函数对元组编号(推荐使用)

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY qid) as rowIndex,* FROM Question
)b
WHERE b.rowIndex>=m AND b.rowindex<=n
上一篇:oracle常用SQL——创建用户、表空间、授权(12C)


下一篇:[转]RabbitMQ入门教程(概念,应用场景,安装,使用)