sql分页

@PageIndex int, --第几页，第一页为1；
@PageSize int, --页面大小；
@TotalCount int OUTPUT, --总条数 输出参数；
 
 DECLARE @StartRowNum int;
 DECLARE @EndRowNum int;
 SET @StartRowNum=(@PageIndex-1)*@PageSize+1;
 SET @EndRowNum=@PageIndex*@PageSize;
 
 SELECT  
		@TotalCount =COUNT(1) 
		FROM   tableName
		
		WITH Temp AS 
		(
			SELECT TOP (@PageSize*@PageIndex) 
				ID,
				ROW_NUMBER() OVER (ORDER BY ID DESC)  AS RowNum  
			FROM tableName			 
				   
		)
 SELECT 
			*      
		FROM Temp AS T 
		INNER JOIN tableName tb ON T.Id=tb.Id 
		WHERE T.RowNum BETWEEN @StartRowNum AND @EndRowNum 
		ORDER BY RowNum 
		或者直接
		SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID DESC)  AS RowNum   FROM tbName) a where RowNum>=@StartRowNum  and RowNum<= @EndRowNum 

 

按后面一种写法 也就是一条正常的sql语句，只要取得Orderby的字段，将row_number()拼接到他的select列中。然后select一下就行了。

C#代码如下

 

 public static string GetPagedSql(string sql, int pageIndex, int pageSize )
      {
          if (String.IsNullOrEmpty(sql))
          {
              return null;
          }      
              string start = "0";
              if (pageIndex > 0)
              {
                  start = Convert.ToString(pageIndex * pageSize);
              }
              int select =  sql.GetSymPosition("select");                  
                  string end = Convert.ToString((pageIndex + 1) * pageSize);
                  string orderSql = sql.GetStrBySym("order by");
                  //fromwhere部分 含from、join及where部分group by 但不含order by
                  string fromSql =  sql.GetStrBySym( "from"); 
                  //select部分 含select
                  string selectSql = "select " + sql.Substring(select, sql.Length - select - fromSql.Length);
                  if (!String.IsNullOrEmpty(orderSql))
                  {
                      fromSql = fromSql.Substring(0, fromSql.Length - orderSql.Length);
                  }
                  else
                  {
                      throw new Exception( " sql2005 怎么着也得弄个order by啊");
                  }
                  //合并成分页SQL
                  string strSql = "select * from (" + selectSql;
                  //order部分 含 order by
                  string rownum  = "row_number()";
                   
                  strSql += "," + rownum + " over (" + orderSql + ") as rn " + fromSql
                         + ") as data where rn>" + start + " and rn<=" + end;
                  if (!String.IsNullOrEmpty( fromSql.GetStrBySym( "group by")) )
                 
                  {
                      fromSql = " from (select count(*) totalCount " + fromSql + ") tbl";
                  }
                  return strSql + ";select count(*) as totalCount " + fromSql;
          
      }

 public static  class ExtMethod
    {
        public static int GetCount(this string str, string sym)
        {
            if (String.IsNullOrEmpty(str) || String.IsNullOrEmpty(sym))
            {
                return 0;
            }
            else
            {
                return str.Length - str.Replace(sym, "").Length;
            }
        }
        public static int GetSymPosition(this string str,string sym)
        {
            if (String.IsNullOrEmpty(str) || String.IsNullOrEmpty(sym))
            {
                return 0;
            }
            else
            {
                Regex regex = new Regex(@"^[\s\n]*?(?<key>" + sym + @")[\[\(\s\n]+?.*?$", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Compiled);
                if (regex.IsMatch(str))
                {
                    return regex.Match(str).Groups["key"].Index + sym.Length;
                }
                else
                {
                    return 0;
                }
            }
        }
        public static string GetStrBySym(this string str,string sym)
        {
            if (String.IsNullOrEmpty(str) || String.IsNullOrEmpty(sym))
            {
                return null;
            }
            else
            {
                string result = null;
                Regex regex = new Regex(@"^.*?[\]\)\s\n\*]+?(?<key>" + sym + @")[\[\(\s\n\*]+?.*?$", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Compiled);
                if (regex.IsMatch(str))
                {
                    bool isHas = false;
                    result = str;
                    while (regex.IsMatch(result))
                    {
                        result = result.Substring(regex.Match(result).Groups["key"].Index);
                        if (GetCount(result, "(") == GetCount(result, ")"))
                        {
                            isHas = true;
                            break;
                        }
                    }
                    if (!isHas)
                    {
                        result = null;
                    }
                }
                return result;
            }
        }
    }