查询N-M条记录。
select IDENTITY(int,1,1) as iid,* into #temptable from yourtable
select top M-N * from #temptable where iid>=NOR:select top M-N * from yourTable where id not in(select top N-1 id from table)
ID为表具有唯一值的任何字段
select IDENTITY(int,1,1) as iid,* into #temptable from yourtable
select top M-N * from #temptable where iid>=NOR:select top M-N * from yourTable where id not in(select top N-1 id from table)
ID为表具有唯一值的任何字段
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)建议:CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)建议:CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [standard search criteria]
AND ProductID > @lastProductID
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO
{
this.Mycomm_Init();//初始化
this.reader_Close();//先关闭
string sql;
if(desc)
{
sql="select "+columns+" from "+tableName+" where "+PK+" in (select top "+pageSize.ToString()+" "+PK+" from "+tableName+" where "+PK+" in(select top "+(pageSize*(currentPageIndex+1)).ToString()+" "+PK+" from "+tableName+" where "+condition+" order by "+PK+" Desc) order by "+PK+") order by "+PK+" Desc";
}
else
{
sql="select "+columns+" from "+tableName+" where "+PK+" in (select top "+pageSize.ToString()+" "+PK+" from "+tableName+" where "+PK+" in(select top "+(pageSize*(currentPageIndex+1)).ToString()+" "+PK+" from "+tableName+" where "+condition+" order by "+PK+") order by "+PK+" desc) order by "+PK;
}
this.Mycomm.CommandText=sql;
this.Myconn_Open();//打开连接
this.reader=this.Mycomm.ExecuteReader();
return this.reader;
}