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

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

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追答
追问：<%
sql="select top 10* from News where news_class_id=109 order by news_id desc "
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,1
for i=1 to rs.recordcount
%>
我这个好像是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"
这条有用，谢谢！

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

SELECttop 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;
追问：<%
sql="select top 10* from News where news_class_id=109 order by news_id desc "
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,1
for i=1 to rs.recordcount
%>
我这个好像是access+asp数据库，该怎么写？
追答：sql="SELECt * FROM ( SELECttop 10*FROM ( SELECt top 20 * FROM News order by news_id desc) order by news_id asc ) order by id desc "

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上测试通过
追问：<%
sql="select top 10* from News where news_class_id=109 order by news_id desc "
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,1,1
for i=1 to rs.recordcount
%>
我这个好像是access+asp数据库，该怎么写？