分页SQL

第一种：
public static string GeneratePage(string fields,string table, string where,string order,int istart,int pagesize)
        {
            string execsql = String.Format("SELECT TOP {0} * FROM\n (\nSELECT {1},row_number() over(order by {2}) as row FROM {3} WHERE {4} \n) A \n WHERE row > {5}", pagesize.ToString(), fields, order, table,where, istart.ToString());
            HttpContext.Current.Trace.Warn(execsql);
            return execsql;
        }

使用方法
 string order = " questionlastupdate DESC ";
           string where = "";
           if (examid > -1)
               where += (" examid = " + examid.ToString() + " AND ");
           if (courseid > -1)
               where += (" courseid= " + courseid.ToString() + " AND ");
           if (categoryid > -1)
               where += (" categoryid= " + categoryid.ToString() + " AND ");
           if (keyword.Length > 0)
               where += String.Format(" title LIKE '%{0}%' AND ", keyword);
           if (complete >-1)
               where += (" complete= " + complete.ToString() + " AND ");
           if (recommend >-1)
               where += (" recommend= " + recommend.ToString() + " AND ");
           if (questionlastupdate !="")
               where += (" questionlastupdate= " + questionlastupdate + " AND ");
           if (answerlastupdate!="")
               where += (" answerlastupdate= " + answerlastupdate + " AND ");
           where += " parentid=-1 ";
           string SQL = DbHelper.GeneratePage("*", "o_olqa", where, order, istart, pagesize);
第二种
如下：
with Records as
      (
      select A.SysNo,A.MailAddress,A.MailSubject,A.MailBody,A.Status,row_number() over (order by A.SysNo asc) as CountNum
      from AsyncEmail A with (nolock)
        )
      select *, (select count(1) from Records with (nolock)) allCount
      from Records with (nolock)
      where CountNum between 0 and 20 and Records.Status=0