手写分页sql_sql 分页的两种写法

string Strsql = string.Format(@"select ee.DOCUMENTNO,ee.APPLICANTNAME,ee.COMPANY,ee.REQUESTTIME,ee.REQ_TITLE, ee.INCIDENT,

(datediff(d,getdate(),STARTTIME)) STARTTIME,cc.STEPLABEL

,isNull((select distinct USERNAME from ORG_USER where LOGINNAME = substring(ASSIGNEDTOUSER,8,len(ASSIGNEDTOUSER)-7)),

substring(ASSIGNEDTOUSER,8,len(ASSIGNEDTOUSER)-7)) ASSIGNEDTOUSER, (datediff(d,getdate(),REQUESTTIME))AS DAYTIME,

BB.ROWNUMBER

from

(select AA.*, ROW_NUMBER() OVER(ORDER BY INCIDENT ) AS ROWNUMBER

from (Select distinct tt.INCIDENT, tt.STEPLABEL From UltimusDB.dbo.TASKS tt,PFT_REQ_MSTR reqmstr

where tt.INCIDENT = reqmstr.INCIDENT

and tt.STEPLABEL in ('问题接收','处理行动反馈','问题经验总结','客户反馈结果')

and tt.STATUS=1) AA)

BB, UltimusDB.dbo.TASKS CC,PFT_REQ_MSTR ee

Where BB.ROWNUMBER between ({0}-1)*{1}+1 and {2}*{3}

and cc.INCIDENT = BB.INCIDENT

and cc.INCIDENT = ee.INCIDENT

and cc.STEPLABEL in ('问题接收','处理行动反馈','问题经验总结','客户反馈结果')

and cc.STATUS=1

order by bb.ROWNUMBER", pageIndex, pageSize, pageIndex, pageSize);

-----

string Strsql = string.Format(@"SELECT TOP {0} * FROM (select DOCUMENTNO,APPLICANTNAME,a.COMPANY,REQUESTTIME,REQ_TITLE,a.INCIDENT, (datediff(d,getdate(),STARTTIME)) STARTTIME,

STEPLABEL,

isNull((select distinct USERNAME from ORG_USER where LOGINNAME = substring(ASSIGNEDTOUSER,8,len(ASSIGNEDTOUSER)-7)),

substring(ASSIGNEDTOUSER,8,len(ASSIGNEDTOUSER)-7)) ASSIGNEDTOUSER ,(datediff(d,getdate(),REQUESTTIME)) AS DAYTIME ,

ROW_NUMBER() OVER (ORDER BY REQUESTTIME desc) AS RowNumber

from ProfitBPMProcess.dbo.PFT_REQ_MSTR a left join UltimusDB.dbo.TASKS b

on a.INCIDENT=b.INCIDENT where b.STATUS=1) A WHERE RowNumber > {1}*({2}-1) order by REQUESTTIME,APPLICANTNAME desc", pageSize, pageSize, pageIndex);