selecttop PageSize SelectedFields fromTablewhere KeyField>( selectmax(KeyField) from ( selecttop PageSize*PageNo id fromTable where1=1 and (Conditions) orderby KeyField ) as t ) and (Conditions) orderby KeyField
在ASP中写成一个函数
PageNo 页数
PageSize 页大小
SelectedFields 要显示的列
KeyField 排序列
Table 表名
Conditions 附加条件
orderDirection 排序方向 0- 升序 1-降序
function genSQL(PageNo,PageSize,SelectedFields,KeyField,Table,Conditions,orderDirection) dim arrOrder '与排序相关的设置 dim strSQLTemplate 'SQL语句模板 dim strSQLResult '返回的SQL语句 arrOrder =array(_ array("","max",">"),_ array("desc","min","<")_ ) '分别是升序和降序下需要使用的模板 strSQLTemplate = _ "select top [-[P_SIZE]-] [-[F_SHOW]-] from [[-[T]-]] "& _ "where [[-[F_KEY]-]][-[O_SIGN]-]( "& _ "select [-[O_TYPE]-]([[-[F_KEY]-]]) from ( "& _ "select top [-[P_BEFORE]-] ([[-[F_KEY]-]]) from [[-[T]-]] "& _ "where ([-[C]-]) "& _ "order by [[-[F_KEY]-]] [-[O_DIR]-]"& _ ") as t "& _ ") "& _ "and ([-[C]-]) "& _ "order by [[-[F_KEY]-]] [-[O_DIR]-]"'SQL语句模板 '进行替换 strSQLResult =Replace(strSQLTemplate,"[-[P_SIZE]-]",PageSize) strSQLResult =Replace(strSQLResult,"[-[P_BEFORE]-]",(PageNo-1) * PageSize) strSQLResult =Replace(strSQLResult,"[-[T]-]",Table) strSQLResult =Replace(strSQLResult,"[-[F_SHOW]-]",SelectedFields) strSQLResult =Replace(strSQLResult,"[-[F_KEY]-]",KeyField) strSQLResult =Replace(strSQLResult,"[-[O_SIGN]-]",arrOrder(orderDirection)(2)) strSQLResult =Replace(strSQLResult,"[-[O_TYPE]-]",arrOrder(orderDirection)(1)) strSQLResult =Replace(strSQLResult,"[-[O_DIR]-]",arrOrder(orderDirection)(0)) strSQLResult =Replace(strSQLResult,"[-[C]-]",Conditions) genSQL = strSQLResult end function
selecttop20 id,c from[test] where[id]>( selectmax([id]) from ( selecttop180 ([id]) from[test] where (1=1) orderby[id] ) as t ) and (1=1) orderby[id]
减肥版 -_-!!
function genSQL2(P,PS,SF,KF,T,C,OD) dim aO,sT,sR '分别是升序和降序下需要使用的模板 aO =array(array("","max",">"),array("desc","min","<")) 'SQL语句模板 sT ="select top [-[PS]-] [-[FS]-] from [[-[T]-]] where [[-[FK]-]][-[OS]-](select [-[OT]-]([[-[FK]-]]) from (select top [-[PB]-] ([[-[FK]-]]) from [[-[T]-]] where ([-[C]-]) order by [[-[FK]-]] [-[OD]-]) as t ) and ([-[C]-]) order by [[-[FK]-]] [-[OD]-]" '进行替换 sR =Replace(Replace(Replace(Replace(Replace(sT,"[-[PS]-]",PS),"[-[PB]-]",(P-1) * PS),"[-[T]-]",T),"[-[FS]-]",SF),"[-[FK]-]",KF) sR =Replace(Replace(Replace(Replace(sR,"[-[OS]-]",aO(OD)(2)),"[-[OT]-]",aO(OD)(1)),"[-[OD]-]",aO(OD)(0)),"[-[C]-]",C) genSQL2 = sR end function
工作空隙写了一个分页SQL语句模板,并改造成了一个ASP/VBScript函数,方便使用基本原型是: select top PageSize SelectedFields from Table where KeyField>( select max(KeyField) from ( select top PageSize*PageNo id from Table where