sql数据库的几种分页sql

TOP分页

select top 10 * from Table1
where Id not in(select top 开始的位置 Id from Table1)

 MAX分页

select top 10 * from Table1
where Id>(select max(Id)
from (select top 开始位置 Id from Table1order by Id)tt)

row分页

select *
from (
    select row_number()over(order by tempColumn)tempRowNumber,*
    from (select top (当前页*每页记录数) tempColumn=0,* from Table1)t
)tt
where tempRowNumber>((当前页-1)*每页记录数)

 

/// <summary>
    /// ACCESS分页
    /// </summary>
    /// <param name="strWhere">条件</param>
    /// <param name="pagesize">每页显示条数</param>
    /// <param name="pageindex">第几页</param>
    /// <param name="orderCol">排序</param>
    /// <param name="TableName">表名</param>
    /// <returns>返回SQL语句</returns>
    public static string GetPager(string strWhere, int pagesize, int pageindex, string orderCol, string TableName)
    {
        if (pageindex == 1)
        {
            string sql = "";
 
            if (!strWhere.Trim().Equals(""))
            {
                sql = "select top " + pagesize.ToString() + " * from " + TableName.ToString() + " where " + strWhere + " order by " + orderCol.ToString() + " DESC";
            }
            else
            {
                sql = "select top " + pagesize.ToString() + " * from " + TableName.ToString() + " order by " + orderCol.ToString() + " DESC";
            }
            return sql;
        }
        else
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("select top {0} * from {1} ", pagesize, TableName);
            strSql.AppendFormat(" where  {1} not in (select top {0} id from {2} ", pagesize * (pageindex - 1), orderCol, TableName);
            if (!strWhere.Trim().Equals(""))
            {
                strSql.AppendFormat(" where {0} order by {1} DESC) and  {0}", strWhere, orderCol);
            }
            else
            {
                strSql.AppendFormat(" order by {0} DESC) ", orderCol);
            }
            strSql.AppendFormat(" order by {0} DESC", orderCol);
            return strSql.ToString();
        }
    }

 

select *
from (
    select row_number()over(order by getdate())tempRowNumber,*
    from (select  * from 表名)a
)b
where tempRowNumber BETWEEN 1 AND 2