我在存储过程中,通过拼接字符串得到了一段sql语句
set @strSql='select cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID ' EXEC Sp_executesql @strsql执行结果为100条记录, 我想再增加一个分页,传入参数 @startRecordIndex 和@pageSize 直接在字符串中就将分页功能实现。 如传入参数 @startRecordIndex=20 @pagesize=10 从第20条记录开始显示10条, 查询结果就为需要的10条记录。。 谢谢高手们解答一下
set @strSql='select cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID ' EXEC Sp_executesql @strsql执行结果为100条记录, 我想再增加一个分页,传入参数 @startRecordIndex 和@pageSize 直接在字符串中就将分页功能实现。 如传入参数 @startRecordIndex=20 @pagesize=10 从第20条记录开始显示10条, 查询结果就为需要的10条记录。。 谢谢高手们解答一下
select top 10 * from (select top 30 * from zhiwu_table order by id) as dd order by id desc
@pagesize int
set @startRecordIndex=20
set @pagesize=10 set @strSql='select top('+@pagesize+')cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID where dbo.tbPortAutoComplte.ID>'+@startRecordIndex EXEC Sp_executesql @strsql
set @strSql='select cc.*,tbPortAutoComplte.PortName_chs from (select top 100 UserID,Name,CnName,SOG,InPort from VWShipInfo where ShipType=1 and (DATEDIFF(s, ReceiveTime, GETDATE()) <= 12*60*60) ) as cc LEFT OUTER JOIN dbo.tbPortAutoComplte ON cc.InPort = dbo.tbPortAutoComplte.ID ';
with t(c1,c2) as
(
EXEC Sp_executesql @strsql
)
select top @pagesize * from t where c1 not int(select top (@startRecordIndex-1) c1 from t order by c1)order by c1
dbo.tbPortAutoComplte.ID>'+@startRecordIndex 其中的startRecordIndex 不是记录的ID 是记录数, 例如我已经显示到19条了, 这次从第20条记录显示。。 我觉这个语句还是有些问题呢