一. SQLServer2008 分页查询
select * from (
select t.*,r.strclipid,r.nsoftcheck , ROW_NUMBER() OVER(ORDER BY nSectionIndex ASC, nBidIndex ASC, nLidIndex ASC ) as R from
(SELECT t_ProgramSheet.strPSID, t_ProgramSheet.strSectionGuid, t_ProgramSheet.strChnID, t_ProgramSheet.tPlayDate,
t_ProgramSheet.strUserID, t_ProgramSheet.nState, t_ProgramSheet.nPGMLockState,t_ProgramSheet.nSubmitState,
t_ProgramSheet.strRemark AS strPlayListRemark, t_ProgramSheet.tLastModifyTime, t_ProgramSheet.strPlayListVersion,
t_ProgramSheet.nVersion, t_ProgramSheet.tCreateTime, t_ProgramSheet.strLastModifyUser, t_ProgramSheet.strLastAuditUser,
t_ProgramSheet.tLastAuditTime, t_PlaylistCompile.strPGMGuid, t_PlaylistCompile.strPackGuid,
t_PlaylistCompile.nIndex AS nBidIndex, t_PlaylistCompile.strPGMName, t_PlaylistCompile.tPGMSchPlayDate,
t_PlaylistCompile.nPGMSchStartTime, t_PlaylistCompile.strPGMDuration, t_PlaylistCompile.nPGMPlayStyle,
t_PlaylistCompile.strRedundantDeviceTypeID, t_PlaylistCompile.strDeviceTypeID, t_PlaylistCompile.nPGMPlayTitle,
t_PlaylistCompile.nPGMPlayLogo, t_PlaylistCompile.nPGMPlayTransType, t_PlaylistCompile.nPGMPlayTransRate,
t_PlaylistCompile.strExtPar AS strBidExtPar, t_PlaylistCompile.nReplay, t_PlaylistCompile.strColumnID, t_PlaylistCompile.nDelete,
t_PlaylistCompile.strVideoSrcDevID, t_PlaylistCompile.nPGMDuration, t_PlaylistCompile.strRemark AS strBidRemark,
t_PlaylistCompile.nPGMLock, t_BIDMapCompile.nIndex AS nLidIndex,
t_BIDMapCompile.strPrimaryID, t_BIDMapCompile.strRedundantID, t_BIDMapCompile.nTapeType, t_BIDMapCompile.nClipType,
t_BIDMapCompile.strSOM, t_BIDMapCompile.strEOM, t_BIDMapCompile.strDuration, t_BIDMapCompile.strClipName,
t_BIDMapCompile.strExtPar AS strLidExtPar,
t_BIDMapCompile.strDubSOM, t_BIDMapCompile.strDubEOM, t_BIDMapCompile.nSOM,
t_BIDMapCompile.nDuration, t_BIDMapCompile.strTapeID, t_BIDMapCompile.nTapeSOM, t_BIDMapCompile.nSubBidID,
t_BIDMapCompile.strSubBidName, t_SectionProperty.nSectionIndex, t_SectionProperty.strSectionName,
t_SectionProperty.nBeginTime, t_SectionProperty.strTempGuid, t_SectionProperty.nEndTime
FROM t_SectionProperty INNER JOIN t_ProgramSheet ON t_SectionProperty.strSectionGuid = t_ProgramSheet.strSectionGuid LEFT OUTER JOIN
t_BIDMapCompile RIGHT OUTER JOIN t_PlaylistCompile ON t_BIDMapCompile.strPGMGuid = t_PlaylistCompile.strPGMGuid ON
t_ProgramSheet.strPSID = t_PlaylistCompile.strPSID
WHERE t_ProgramSheet.strSectionGuid IN ('7E9058EA-DE6E-43b9-B885-EF73786DCA06') AND t_ProgramSheet.strChnID = 'CHN09' AND tPlayDate = '2016-07-13' )
t LEFT OUTER JOIN ( select * from T_BVSID WHERE nDelete = 0 AND nReady = 1 AND nCheck = 1 ) r on t.strprimaryid=r.strcliplogicid
) Q where R>100 AND R<=200
其中100,200分别为数据开始和结束行
二.SqlServer 2012分页查询
SELECT strClipName from t_BVSID ORDER BY strClipName OFFSET 100 ROW FETCH NEXT 50 ROW ONLY