Question

SQL中排序后从第10个数据开始取5个数据怎么写?

select top 5 * from table order by score desc 是排序后取前5个, 我现在希望排序后取从第十位开始取前五位..

请帮忙!!
能详细点吗? 不是很懂....初学不好意思.

Answer 1

--SQL Server 2000
select top (5) *
from ( select top 15 * from 表 order by 排序列 desc ) as a
order by 排序列 desc

--SQL Server 2005,2008,2008R2,2012通用方法
select *
from (
select *,row_number() over (order by 排序列 desc) as id
from 表
) a
where id between 10 and 15
order by id

--SQL Server 2012

select * from 表 order by 排序列 desc OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY

Answer 2

加一个标识列
然后用ID判断.

补:
改表结构,如果这个表里面没有一个合适做主键就以一个ID列做为主键,然后你可以将ID列设置为自动增长(identity(1,1))

这个ID列我们的叫发就是标识列.

Answer 3

select *,row_number() over(order by score desc) as listid from table where (row_number() over(order by score desc))>10 and (row_number() over(order by score desc))<16
sql2005的写法
select top 5 * from table where score not in (select top 10 score from table order by score desc) order by score desc
sql2000的写法

Answer 4

select top 5 栏位名 from table order by score desc where 栏位名 not in (select top 9 栏位名 from table order by score desc)

最简便的方法