求一个 联合查询的分页存储过程

Question

求一个 联合查询的分页存储过程
带好注释，具体用法，
谢各位高手了

Answer 1

http://www.cnblogs.com/wang123/archive/2006/08/05/468717.html
不多少，自己看，百万级别的分页。

Answer 2

我给你一个工具类
你添加到项目直接用就可以了，代码你可以自己看看，这是原来公司同事一起写的工具类// <summary>
    /// /*分页存储过程*/
    /// </summary>
    public class PageInfo
    {
        /// <summary>
        /// 要显示的表或多个表的连接
        /// </summary>
        public string TableName;
        /// <summary>
        /// 主表的主键
        /// </summary>
        public string IDName;
        /// <summary>
        /// 要显示的字段列表
        /// </summary>
        public string FieldNameList = "*";
        /// <summary>
        /// 每页显示的记录个数
        /// </summary>
        public int PageSize = 10;
        /// <summary>
        /// 当前显示页数
        /// </summary>
        public int CurrentPage = 1;
        /// <summary>
        /// 排序字段列表或条件
        /// </summary>
        public string FieldSort = null;
        /// <summary>
        /// 排序方法，0为升序，1为降序
        /// </summary>
        public byte SortType = 0;
        /// <summary>
        /// -查询条件,不需where
        /// </summary>
        public string strCondition = null;
        /// <summary>
        /// DS表名
        /// </summary>
        public string DsTabelName = "Table1";
        /// <summary>
        /// 统计
        /// </summary>
        public string GroupBy = "";
        /// <summary>
        /// 连表
        /// </summary>
        public string JoinTable = "";
        /// <summary>
        /// 是否显示页数
        /// </summary>
        public int isShowPageCount = 1;
        /// <summary>
        /// 是否启用脏读
        /// </summary>
        public int IsNoLock = 0;
        /// <summary>
        /// 返回结果分页后的总页数
        /// </summary>
        public int PageCounts = 1;
        /// <summary>
        /// 返回查询到的记录数
        /// </summary>
        public int recordCounts = 1;        /// <summary>
        /// 连表字段
        /// </summary>
        public string JoinFieldNameList = "";
        /// <summary>
        /// 默认的连接字符串
        /// </summary>
        public string ConnectionString = Configuration.MetooSystemConnectionString;        public string VarConnectionString
        {
            get { return ConnectionString; }
            set { ConnectionString = value; }
        }
        /// <summary>
        /// 分页存储过程构造函数
        /// </summary>
        /// <param name="TableName">要显示的表或多个表的连接</param>
        /// <param name="IDName">主表的主键</param>
        public PageInfo(string TableName, string IDName)
        {
            this.TableName = TableName;
            this.IDName = IDName;
            this.DsTabelName = TableName;
        }        /// <summary>
        /// 分页存储过程构造函数
        /// </summary>
        /// <param name="TableName">要显示的表或多个表的连接</param>
        /// <param name="IDName">主表的主键</param>
        /// <param name="ConnectionString">连接字符串</param>
        public PageInfo(string TableName, string IDName, string ConnectionString)
        {
            this.TableName = TableName;
            this.IDName = IDName;
            this.DsTabelName = TableName;
            this.ConnectionString = ConnectionString;
        }        /// <summary>
        /// 分页存储过程构造函数
        /// </summary>
        /// <param name="TableName">要显示的表或多个表的连接</param>
        /// <param name="IDName">主表的主键</param>
        /// <param name="ConnectionString">连接字符串</param>
        /// <param name="CurrentPage">当前页</param>
        public PageInfo(string TableName, string IDName, string ConnectionString, int CurrentPage)
        {
            this.TableName = TableName;
            this.IDName = IDName;
            this.DsTabelName = TableName;
            this.CurrentPage = CurrentPage;
            this.ConnectionString = ConnectionString;
        }
        /// <summary>
        /// 分页存储过程构造函数
        /// </summary>
        /// <param name="TableName">要显示的表或多个表的连接</param>
        /// <param name="IDName">主表的主键</param>
        /// <param name="DataSetTabelName">表名</param>
        /// <param name="ConnectionString"></param>
        /// <param name="CurrentPage">当前页</param>
        public PageInfo(string TableName, string IDName, string DataSetTabelName, string ConnectionString, int CurrentPage)
        {
            this.TableName = TableName;
            this.IDName = IDName;
            this.DsTabelName = DataSetTabelName;
            this.CurrentPage = CurrentPage;
            this.ConnectionString = ConnectionString;
        }        /// <summary>
        /// 主函数：执行分页，以ID排序
        /// </summary>
        /// <returns></returns>
        public DataSet dsPageList()
        {            if (isShowPageCount == 1)
            {
                recordCounts = GetRecordCounts();                if (recordCounts <= PageSize)
                {
                    PageCounts = 1;
                }
                else
                {
                    PageCounts = Convert.ToInt32(Math.Ceiling(recordCounts / (PageSize + .0)));
                }
            }            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, CSqlStr());
        }        public SqlDataReader drPageList()
        {
            if (isShowPageCount == 1)
            {
                recordCounts = GetRecordCounts();                if (recordCounts <= PageSize)
                {
                    PageCounts = 1;
                }
                else
                {
                    PageCounts = Convert.ToInt32(Math.Ceiling(recordCounts / (PageSize + .0)));
                }
            }            return SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, CSqlStr());
        }        string CSqlStr()
        {
            string strGroup = "";
            if (!string.IsNullOrEmpty(GroupBy))
            {
                strGroup = " group by " + GroupBy;
            }
            string strSort = "";
            if (SortType == 1)
            {
                strSort = " DESC";
            }            string strFldSort = "";
            if (!string.IsNullOrEmpty(FieldSort))
            {
                strFldSort = " Order By " + FieldSort + " " + strSort;
            }
            else
            {
                strFldSort = " Order by (select 1)";
            }            string strCond = "";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strCond = " Where " + strCondition;
            }            if (CurrentPage == 0)
            {
                CurrentPage = 1;
            }            string strNoLock = "";            if (IsNoLock == 1)
            {
                strNoLock = " With(NoLock) ";
            }            string pagestr = "";            if (!string.IsNullOrEmpty(JoinTable) && !string.IsNullOrEmpty(JoinFieldNameList))
            {
                pagestr = "select RowID,{7} from (SELECT * FROM ( SELECT ROW_NUMBER() OVER({0}) AS RowID,{1} FROM {2} {9} {3} {4} ) as a  WHERE RowID BETWEEN {5} AND {6}) a {8} Order By RowID";
                return string.Format(pagestr, strFldSort, FieldNameList, TableName, strCond, strGroup, ((CurrentPage - 1) * PageSize + 1), (CurrentPage * PageSize), JoinFieldNameList, JoinTable, strNoLock);
            }
            else
            {
                pagestr = "SELECT * FROM ( SELECT ROW_NUMBER() OVER({0}) AS RowID,{1} FROM {2} {7} {3} {4} ) as a  WHERE RowID BETWEEN {5} AND {6}";
                return string.Format(pagestr, strFldSort, FieldNameList, TableName, strCond, strGroup, ((CurrentPage - 1) * PageSize + 1), (CurrentPage * PageSize), strNoLock);
            }        }        /// <summary>
        /// 获取总记录数
        /// </summary>
        /// <returns></returns>
        int GetRecordCounts()
        {
            string strGroup = "";
            if (!string.IsNullOrEmpty(GroupBy))
            {
                strGroup = " group by " + GroupBy;
            }            string strNoLock = "";            if (IsNoLock == 1)
            {
                strNoLock = " With(NoLock) ";
            }            string strCond = "";
            if (!string.IsNullOrEmpty(strCondition))
            {
                strCond = " Where " + strCondition;
            }            string strSql = "";            if (string.IsNullOrEmpty(GroupBy))
            {
                strSql = string.Format("select count(*) from {0}  {2} {1}", TableName, strCond, strNoLock);
            }
            else
            {
                strSql = string.Format("select count(*) from (select count(*) as l from {0} {2} {1} {3}) a", TableName, strCond, strNoLock, strGroup);
            }            object i = SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, strSql);            if (object.Equals(i, null))
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(i);
            }
        }
    }类中有使用到了SqlHelper中的方法  SqlHelper类 你自己去下载下面是调用示例：        public static DataSet GetPageBylist(查询条件, int currPage, int pageSize, out int recordCount)
        {
            PageInfo pager = new PageInfo("主表名称", "主键");
            pager.FieldNameList = "主表字段2, 主表字段3, 主表字段4, 主表字段5;
            pager.JoinFieldNameList = "主表别名.主表字段1, 主表别名.主表字段2, 主表别名.主表字段3, 连接表别名.连接表字段1, 连接表别名.连接表字段2";
            pager.JoinTable = "Left join 要连接的表名称 AS b ON 主表.主键 = 连接表.连接表关联字段";
            pager.CurrentPage = currPage; //起始页
            pager.PageSize = pageSize;   //页大小
            pager.FieldSort = "AccessID"; //排序字段，可多个 都好分割
pager.strCondition="查询条件" //不需要 where 关键字            DataSet ds = pager.dsPageList();
            recordCount = pager.recordCounts;            return ds;        }

Answer 3

上面用中文写的有点乱，不好意思  怕你看不明白
比如要查询：表tableA  中的id,name 和表tableB中的age (主外键关系id=》id)    表tableA  字段 id,name,age
关联表tableB(别名 b)  字段 id,name,agepager.FieldNameList = "id,name";
pager.JoinFieldNameList = "a.id,a.name,b.age"
pager.JoinTable = "Left join tableBAS b ON a.id=b.id";中间a.id，a.name 分别是tableA.id,tableA.name  这里不用显示取别名 只要和连接表别名一样就可以
你不去别名 直接用原表明区分也是可以的 

Answer 4

cyq.data 里的SelectBase分页存储过程可以直接拿去用。