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];
Select *from ( Select *,row_number() over(order by id asc) as "No" from T_Users ) as T_1 ) where T_1.No>@startRowIndex and T_1.No<= @startRowIndex+@maximumRows
有别名 还是不行 提示多次指定了IDSELECT * FROM (SELECT *,ROW_NUMBER() OVER( ORDER BY RTime ) AS RowNumber FROM Info AS a INNER JOIN Para AS b ON a.ID = b.ID Where 1=1 ) AS RowNumberTableSource WHERE RowNumber BETWEEN 1 AND 50
有别名 还是不行 提示多次指定了IDSELECT * FROM (SELECT *,ROW_NUMBER() OVER( ORDER BY RTime ) AS RowNumber FROM Info AS a INNER JOIN Para AS b ON a.ID = b.ID Where 1=1 ) AS RowNumberTableSource WHERE RowNumber BETWEEN 1 AND 50
方式一: 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 WHERE RowNumber BETWEEN 5 AND 10'exec (@Sql) 方式二: SELECT * FROM (select ROW_NUMBER() OVER (ORDER BY biao_ID) AS RowNumber , * from dbo.biao) as DataList WHERE RowNumber BETWEEN 5 AND 10
这个应该对你有用
http://www.cnblogs.com/kkun/archive/2011/08/15/2139713.html
var dt = dac.ExecuteDataSet(sql);
return dt.Tables[0];
(
Select *,row_number() over(order by id asc) as "No"
from T_Users ) as T_1
)
where T_1.No>@startRowIndex and T_1.No<= @startRowIndex+@maximumRows
网上很多
针对语句建立合适的键/索引
ROW_Number()分页吧,
比较简单也比较高效
FROM
(SELECT *,ROW_NUMBER() OVER( ORDER BY RTime ) AS RowNumber
FROM Info AS a INNER JOIN Para AS b ON a.ID = b.ID Where 1=1 ) AS RowNumberTableSource
WHERE RowNumber BETWEEN 1 AND 50
FROM
(SELECT *,ROW_NUMBER() OVER( ORDER BY RTime ) AS RowNumber
FROM Info AS a INNER JOIN Para AS b ON a.ID = b.ID Where 1=1 ) AS RowNumberTableSource
WHERE RowNumber BETWEEN 1 AND 50
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
WHERE RowNumber BETWEEN 5 AND 10'exec (@Sql)
方式二:
SELECT * FROM
(select ROW_NUMBER() OVER (ORDER BY biao_ID) AS RowNumber , * from dbo.biao) as DataList
WHERE RowNumber BETWEEN 5 AND 10