关于使用sql语句实现分页排序

select top 8 * from  
(
select TOP(100) PERCENT * from Calc_RewardPoints order by CalcMonth DESC) as t1  
where 1=1  and  1=1 AND CalcMonth >='2011-05' AND CalcMonth <= '2020-05' and 
ID not in  ( 
select top 8 ID from  (select TOP(100) PERCENT * from Calc_RewardPoints order by CalcMonth DESC) as t2
  where 1=1  and  1=1 AND CalcMonth >='2011-05' AND CalcMonth <= '2020-05' order by CalcMonth DESC
)  order by CalcMonth DESC

思路如下:
1.选出所有数据
2.选出本页前面所有页面的数据
3.从1中数据删除与2相同的数据
4.从1中的数据选取数据(top +分页大小)

public DataSet GetLogInfo(int pageIndex, int pageSize, string table, string strWhere, string primaryKey, string orderField, string orderFlag)
    {
        try
        {
            string table1 = " (select TOP(100) PERCENT * from " + table + " order by " + orderField + " " + orderFlag + ") as t1 ";
            string table2 = " (select TOP(100) PERCENT * from " + table + " order by " + orderField + " " + orderFlag + ") as t2 ";
            //构建查询语句
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top " + pageSize + " * from " + table1 + " where 1=1 ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }

            strSql.Append(" and " + primaryKey + " not in ");
            strSql.Append(" ( select top " + (pageSize * pageIndex).ToString() + " " + primaryKey + " from " + table2 + " where 1=1 ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }

            strSql.Append(" order by " + orderField + " " + orderFlag + ") ");
            strSql.Append(" order by " + orderField + " " + orderFlag);

            //创建默认数据库实例
            Database db = DatabaseFactory.CreateDatabase("USP30LogConnectionString");

            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());

            //取数据
            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds;
            }
            else
            {
                return ds;
            }
        }
        catch (Exception ex)
        {
            string s = ex.Message;
        }
        return null;
    }