SQL2008或者SQL2005里ROW_NUMBER分页，SQL2000怎么实现等同的效果

下面的代码在SQL2008或者SQL2005能使用ROW_NUMBER分页，

Select * From
                        (
                        Select  ROW_NUMBER() OVER (order by {3}) as 'RowNumber',OrgNo,OperatorNo,Name,Tel,Address,OnJob,MaxCutM,password,OperatorType,Remark FROM Operator where 1=1 {0}
                        )temp
                        where temp.RowNumber between {1} and {2}

SQL2000没有的ROW_NUMBER()怎么实现分页 能给出上面的等同代码？


结合三层架构项目的具体案例实现的操作方法：
 

/// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="Operator">Operator实体</param>
        /// <param name="sOrderFiled">排序字段</param>
        /// <param name="iPageSize">分页大小</param>
        /// <param name="iCurrentPageIndex">当前页</param>
        /// <param name="iCount">总记录数</param>
        /// <returns></returns>
        public DataTable GetPageList(Model.Operator Operator, string sOrderFiled, int iPageSize, int iCurrentPageIndex, out int iCount)
        {
            StringBuilder sbWhere = new StringBuilder();
            List<SqlParameter> lstParas = new List<SqlParameter>();
            string sSql = @" Select * From
                        (
                        Select  ROW_NUMBER() OVER (order by {3}) as 'RowNumber',OrgNo,OperatorNo,Name,Tel,Address,OnJob,MaxCutM,password,OperatorType,Remark FROM Operator where 1=1 {0}
                        )temp
                        where temp.RowNumber between {1} and {2} ";
            string sCountSql = @"Select Count(1) FROM Operator where 1=1 {0}";
            if (!string.IsNullOrEmpty(Operator.OrgNo))
            {
                sbWhere.Append(" and Operator.OrgNo=@OrgNo ");
                lstParas.Add(new SqlParameter("@OrgNo", Operator.OrgNo));
            }
            sCountSql = string.Format(sCountSql, sbWhere.ToString());
            sSql = string.Format(sSql, sbWhere.ToString(), (iCurrentPageIndex - 1) * iPageSize + 1, iCurrentPageIndex * iPageSize, sOrderFiled);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(sSql);
            sbSql.Append(sCountSql);
            DataSet ds = DbHelperSQL.Query(sbSql.ToString(), lstParas);
            iCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }

里面用的都是参数 。能结合这个方法写出来是最好了。谢谢

 

上面方法的语句改成下面

  string sSql = @"select top {4} * from Operator where 1=1 {0} and {3} not in(select top {1} {3} from Operator where 1=1 {0} order by {3}) order by {3} ";   

 

  sSql = string.Format(sSql, sbWhere.ToString(), (iCurrentPageIndex - 1) * iPageSize , iCurrentPageIndex * iPageSize, sOrderFiled, iPageSize);

 

到此处代码的实现效果完成

本文来自: 51模板集(www.51mubanji.com) 详细出处参考：http://www.51mubanji.com/sjk/SQL/10.html