分页SQL

浏览数：31 / 时间：2015年06月12日

一、Oracle

1、SQL

-- order by
SELECT *
  FROM (SELECT ORACLEPAGESQL1.*, ROWNUM RN
          FROM (SELECT *
                  FROM (SELECT IDX, NAME, CH_NAME FROM TBL_USER) ORACLEPAGESQL
                 ORDER BY IDX ASC) ORACLEPAGESQL1
         WHERE ROWNUM <= 20) ORACLEPAGESQL2
 WHERE RN > 10;

-- no order by 
SELECT *
  FROM (SELECT ORACLEPAGESQL1.*, ROWNUM RN
          FROM (SELECT IDX, NAME, CH_NAME FROM TBL_USER) ORACLEPAGESQL1
         WHERE ROWNUM <= 20) ORACLEPAGESQL2
 WHERE RN > 10;

2、CODE

 /// <summary>
        /// 分页获取当前页记录
        /// </summary>
        /// <param name="PageIndex">当前页面</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="strSQL">查询SQL</param>
        /// <param name="strOrderBy">分组</param>
        /// <returns>分页SQL</returns>
        public string GetOraclePageSQL(int PageIndex, int PageSize, string strSQL, string strOrderBy)
        {
            string result = "";
            int startIndex = (PageIndex - 1) * PageSize;
            int endIndex = startIndex + PageSize;
            if (!string.IsNullOrEmpty(strOrderBy))
            {
                result = "SELECT * FROM (" + strSQL + ") oraclePageSQL ORDER BY " + strOrderBy;
            }
            else
            {
                result = strSQL;
            }
            result = "SELECT * FROM (SELECT oraclePageSQL1.*,rownum rn FROM (" + result + ") oraclePageSQL1 WHERE rownum<=" + endIndex + ") oraclePageSQL2 WHERE rn>" + startIndex;

            return result;
        }

        /// <summary>
        /// 分页获取总记录数
        /// </summary>
        /// <param name="strSQL">查询SQL</param>
        /// <returns>总记录数SQL</returns>
        public string GetOraclePageCoutSQL(string strSQL)
        {
            string result = "";
            result = "SELECT COUNT(*) totalNum FROM (" + strSQL + ") TOTALNUM  ";

            return result;
        }

二、MySQL

1、SQL

SELECT * FROM zt_history limit 0,10

2、CODE

        /// <summary>
        /// 获取数据集以及分页总记录数
        /// </summary>
        /// <param name="PageIndex">当前页面</param>
        /// <param name="PageSize">每页记录数</param>
        /// <param name="strSQL">SQL</param>
        /// <param name="count">总记录数</param>
        /// <returns>数据集</returns>
        private DataTable GetMySQLDt(int PageIndex, int PageSize,string strSQL,out int count)
        {
            try
            {
                string strCon = "Data Source=localhost;Database=zentao;User Id=root;Password=;port=3309";
                int intStart = (PageIndex -1) * PageSize;
                string strPageSQL = strSQL + " limit " + intStart + "," + PageSize;
                DataSet ds = MySqlHelper.ExecuteDataset(strCon, strPageSQL);

                string strCountSql = "SELECT COUNT(*) totalNum FROM (" + strSQL + ") TOTALNUM ";
                DataSet dsCount = MySqlHelper.ExecuteDataset(strCon, strCountSql);
                count = Convert.ToInt32(dsCount.Tables[0].Rows[0][0]);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }