求一个速度快的分页SQL
每次 传当前页 和 取的条数
据说 用top和ordery 很慢 有好的方法吗
[解决办法]
用存储过程分页吧 =。= 很快的
[解决办法]
mssql一般都用rownumber了，分页的开销其实都是在排序上，有合适的索引就会快很多
[解决办法]
http://www.cnblogs.com/lli0077/archive/2008/09/03/1282862.html
这个应该对你有用
[解决办法]
[MSSQL]NTILE另类分页有么有?!
http://www.cnblogs.com/kkun/archive/2011/08/15/2139713.html
[解决办法]
string sql = string.Format("select * from (SELECT row_number () over(order by P.id) rowNum,P.id,F.eyerect,P.personName,F.id as id1,F.personid,F.facerect,L.id as id2,R.id as id3,R.labId,R.perId,F.faceNumber,P.fthreshold,F.image,L.name FROM person AS P INNER JOIN perlab AS R ON P.id=R.perId INNER JOIN label AS L ON R.labId=L.id INNER JOIN face AS F ON P.id=F.personid AND P.id=R.perId )temp where rowNum>({0}-1)*{1} and rowNum<={0}*{1}", page, count);
var dt = dac.ExecuteDataSet(sql);
return dt.Tables[0];
[解决办法]
方式一：
DECLARE @Sql varchar(3250)
SET @Sql = 'WITH DataList AS (
select ROW_NUMBER() OVER (ORDER BY biao_ID) AS RowNumber , * from dbo.biao)
SELECT * FROM DataList
WHERERowNumber BETWEEN 5 AND 10'
exec (@Sql)
方式二：
SELECT * FROM
(select ROW_NUMBER() OVER (ORDER BY biao_ID) AS RowNumber , * from dbo.biao) as DataList
WHERERowNumber BETWEEN 5 AND 10
