select top 10 * from tab where id>=90000 and id<900011ID是主键,速度不会慢 否则,怎么写都是慢
select top 每页显示的记录数 * from topic where id not in (select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc) order by id desc with temptbl as ( SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, ... ) SELECT * FROM temptbl where Row between @startIndex and @endIndex
select top 10 * from tab where id>=90000 and id<900011
select * from tab where id BETWEEN 90000 and 900010
如果有主键ID(从1开始递增),用1楼的方法 如果没有主键ID,你的数据库是SQLSERVER2005或2008 用下面的语句SELECT A.* FROM ( SELECT TOP 90010 *, ROW_NUMBER() OVER ( ORDER BY 表.主键字段 DESC ) AS RowNumber FROM 表 WHERE 条件 ) A WHERE A.RowNumber BETWEEN 90001 AND 90010 ORDER BY A.RowNumber
如果你有RowNumber的话,当然使用它。
select top 10 * from tab where id>=90000 and id<900011
select * from 表 where rowid >= 900000 minus select * from 表 where rowid >= 900010;
select 要查询的字段名字 from tab where id BETWEEN 90000 and 900010 不要写*,写字段名字会直接检索到字段值
select top 10 * from tab where id not in (select top 90000 id from tab)
SQL2005 有 记录行遍号的 你可以借助 一下 可以提升一下 速度
top 10和where id>=90000 and id<900011是不是有点重合了?
如果id为不连续,楼上好几楼的sql语句都是错的, 我觉得还是rownumber的好,好像有人测试过,比那个not in 那个快点。
(select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc)
order by id desc with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row,
...
)
SELECT * FROM temptbl where Row between @startIndex and @endIndex
如果没有主键ID,你的数据库是SQLSERVER2005或2008
用下面的语句SELECT A.*
FROM ( SELECT TOP 90010 *,
ROW_NUMBER() OVER ( ORDER BY 表.主键字段 DESC ) AS RowNumber
FROM 表
WHERE 条件
) A
WHERE A.RowNumber BETWEEN 90001 AND 90010
ORDER BY A.RowNumber
minus
select * from 表 where rowid >= 900010;
不要写*,写字段名字会直接检索到字段值
我觉得还是rownumber的好,好像有人测试过,比那个not in 那个快点。