获取sql serverl数据库第10到20条数据

Question

select top 10 * from News where news_class_id=109 order by news_id desc
----这是获取最新10个数据的sql server语句。照着这个改就可以了，不要讲理论，我要拷贝过来直接能用的，先谢了
sql="select top 10 * from News where news_class_id=109 order by news_id desc "
我这个好像是access数据库，该怎么写？ 你们的版本都不对

Answer 1

SQL Server 2008 与以下版本, 可这么写:

SELECT top 10 * FROM
(
select top 20 * from News where news_class_id=109 order by news_id desc

) top_20
order by news_id

SQL Server 2012 可这么写:

select * from News where news_class_id=109 order by news_id desc
OFFSET
10 ROWS
FETCH NEXT
10 ROWS ONLY;

追问

我这个好像是access+asp数据库，该怎么写？

追答

sql="SELECT * FROM ( SELECT top 10 * FROM ( SELECT top 20 * FROM News order by news_id desc) order by news_id asc ) order by id desc "

Answer 2

select * from(
select *, ROW_NUMBER() OVER(order by news_id desc ) as row from News where news_class_id=109
) a
where row between 10 and 20

这个方式条数可以随便取，比较方便，在sql2005上测试通过

追问

我这个好像是access+asp数据库，该怎么写？

Answer 3

2005以上版本适用：
;with cte as(

select *,row_number() over(order by news_id desc)rn from News where news_class_id=109
)select * from cte where rn between 10 and 20

----------------
select top 10 * from News where news_class_id=109
and id not in (select top 10 id from news where news_class_id=109
order by news_id desc)
order by news_id desc

追问

我这个好像是access+asp数据库，该怎么写？

追答

sql="

select top 10 * from News where news_class_id=109
and id not in (select top 10 id from news where news_class_id=109
order by news_id desc)
order by news_id desc"
把语句换掉喽，尝试一下吧

追问

sql="select top 10 * from News where news_class_id=109 and news_id not in (select top 10 news_id from news where news_class_id=109 order by news_id desc) order by news_id desc"
这条有用，谢谢！