SELECT TOP 页大小 * FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A ) ORDER BY id
6楼的是一次性加载内存中,不考虑,不过谢谢顶。 7楼的三种,1.select..top..not in top..如果有连接查询逻辑有点混乱。2.id>id我的ID不是int类型,但是可以根据时间。3.游标估计是有点慢。 我暂时使用的是3个top排序的方法。。貌似也不怎么好。。大虾们帮忙。参与有分。谢谢。
我最近用的分页sql,不过一定要指定主键,top max分页效率还是挺不错的 /// <summary> /// 生成top max分页sql语句,支持多表联查(事理) /// </summary> /// <param name="tableName">表名</param> /// <param name="fields">字段</param> /// <param name="primaryKey">主键</param> /// <param name="pageSize">一页显示多少条</param> /// <param name="currentPage">当前页数</param> /// <param name="where">where条件,无需加where</param> /// <param name="orderBy">order by条件,无需加order by</param> /// <returns>分页sql语句</returns> public static string GetDataPaginationSql(string tableName, string fields, string primaryKey, int pageSize, int currentPage, string where, string orderBy) { //SELECT TOP 10 UserId,AddUserId,Term,SchoolYear FROM SL_StudentsCQECDetail //WHERE CQECId > //( // SELECT MAX(CQECId) FROM // ( // SELECT TOP 10 CQECId FROM SL_StudentsCQECDetail where UserId=479 ORDER BY CQECId // ) AS T //) //and UserId=479 //order by UserId if (currentPage > 0) currentPage--; StringBuilder sql = new StringBuilder(); sql.Append("SELECT TOP " + pageSize + " " + fields + " FROM " + tableName); if (currentPage == 0) { if (!string.IsNullOrEmpty(where)) sql.Append(" WHERE " + where); } else { sql.Append(" WHERE " + primaryKey + ">"); sql.Append("(SELECT MAX(" + primaryKey + ") FROM "); sql.Append("(SELECT TOP " + pageSize * currentPage + " " + primaryKey + " FROM " + tableName); if (!string.IsNullOrEmpty(where)) sql.Append(" WHERE " + where); sql.Append(" ORDER BY " + primaryKey + ") AS T)"); if (!string.IsNullOrEmpty(where)) sql.Append(" AND " + where); } if (!string.IsNullOrEmpty(orderBy)) sql.Append(" ORDER BY " + orderBy); return sql.ToString(); }
/* Function: SuperPaging * Description: * 2000高效性能分页 * Example: * SuperPaging @TableName='表名',@Orderfld='排序列名' */ CREATE PROCEDURE SupesoftPage ( @TableName nvarchar(50), -- 表名 @ReturnFields nvarchar(2000) = '*', -- 需要返回的列 @PageSize int = 10, -- 每页记录数 @PageIndex int = 1, -- 当前页码 @Where nvarchar(2000) = '', -- 查询条件 @Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键 @OrderType int = 1 -- 排序类型 1:降序 其它为升序) AS DECLARE @TotalRecord int DECLARE @TotalPage int DECLARE @CurrentPageSize int DECLARE @TotalRecordForPageIndex int DECLARE @OrderBy nvarchar(255) DECLARE @CutOrderBy nvarchar(255)if @OrderType = 1 BEGIN set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc ' set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc ' END else BEGIN set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc ' set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc ' END -- 记录总数 declare @countSql nvarchar(4000) set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord outSET @TotalPage=(@TotalRecord-1)/@PageSize+1 SET @CurrentPageSize=@PageSize IF(@TotalPage=@PageIndex) BEGIN SET @CurrentPageSize=@TotalRecord%@PageSize IF(@CurrentPageSize=0) SET @CurrentPageSize=@PageSize END -- 返回记录 set @TotalRecordForPageIndex=@PageIndex*@PageSize exec('SELECT * FROM (SELECT TOP '+@CurrentPageSize+' * FROM (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+' FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2 '+@CutOrderBy+') TB3 '+@OrderBy) -- 返回总页数和总记录数 SELECT @TotalPage as PageCount,@TotalRecord as RecordCount GO 2000我都用这个分页存储过程分页面。
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 // 1、定义几个所需的公有成员:
int pageSize = 0; //每页显示行数 int nMax = 0; //总记录数 int pageCount = 0; //页数=总记录数/每页显示行数 int pageCurrent = 0; //当前页号 int nCurrent = 0; //当前记录行 DataSet ds = new DataSet(); DataTable dtInfo = new DataTable();
//2、在窗体载入事件中,从数据源读取记录到DataTable中:
string strConn = "SERVER=127.0.0.1;DATABASE=NORTHWIND;UID=SA;PWD=ULTRATEL"; //数据库连接字符串 SqlConnection conn = new SqlConnection(strConn); conn.Open(); string strSql = "SELECT * FROM CUSTOMERS"; SqlDataAdapter sda = new SqlDataAdapter(strSql,conn); sda.Fill(ds,"ds"); conn.Close(); dtInfo = ds.Tables[0]; InitDataSet();
绑定到 datagridview 在分页还是直接在数据库分页
绑定到 datagridview 在分页还是直接在数据库分页
我只会这个。。帮你找到代码。。这个符合你的要求不
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
) A
)
ORDER BY id
7楼的三种,1.select..top..not in top..如果有连接查询逻辑有点混乱。2.id>id我的ID不是int类型,但是可以根据时间。3.游标估计是有点慢。 我暂时使用的是3个top排序的方法。。貌似也不怎么好。。大虾们帮忙。参与有分。谢谢。
/// 生成top max分页sql语句,支持多表联查(事理)
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fields">字段</param>
/// <param name="primaryKey">主键</param>
/// <param name="pageSize">一页显示多少条</param>
/// <param name="currentPage">当前页数</param>
/// <param name="where">where条件,无需加where</param>
/// <param name="orderBy">order by条件,无需加order by</param>
/// <returns>分页sql语句</returns>
public static string GetDataPaginationSql(string tableName, string fields, string primaryKey,
int pageSize, int currentPage, string where, string orderBy)
{
//SELECT TOP 10 UserId,AddUserId,Term,SchoolYear FROM SL_StudentsCQECDetail
//WHERE CQECId >
//(
// SELECT MAX(CQECId) FROM
// (
// SELECT TOP 10 CQECId FROM SL_StudentsCQECDetail where UserId=479 ORDER BY CQECId
// ) AS T
//)
//and UserId=479
//order by UserId if (currentPage > 0)
currentPage--; StringBuilder sql = new StringBuilder();
sql.Append("SELECT TOP " + pageSize + " " + fields + " FROM " + tableName);
if (currentPage == 0)
{
if (!string.IsNullOrEmpty(where))
sql.Append(" WHERE " + where);
}
else
{
sql.Append(" WHERE " + primaryKey + ">");
sql.Append("(SELECT MAX(" + primaryKey + ") FROM ");
sql.Append("(SELECT TOP " + pageSize * currentPage + " " + primaryKey + " FROM " + tableName);
if (!string.IsNullOrEmpty(where))
sql.Append(" WHERE " + where);
sql.Append(" ORDER BY " + primaryKey + ") AS T)");
if (!string.IsNullOrEmpty(where))
sql.Append(" AND " + where);
}
if (!string.IsNullOrEmpty(orderBy))
sql.Append(" ORDER BY " + orderBy);
return sql.ToString();
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using HxwcBLL;namespace HxwcWebUI
{
public partial class IntroductionInfo : System.Web.UI.Page
{
/// <summary>
/// 当前页数
/// </summary>
protected int cPage;
/// <summary>
/// 总共有多少页
/// </summary>
protected int pCount;
/// <summary>
/// 总共有多少条记录
/// </summary>
protected int rCount;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataList(1);
} }
/// <summary>
/// 为DataList绑定数据
/// </summary>
/// <param name="pageNum">要查看的页数</param>
protected void BindDataList(int pageNum)
{
cPage = pageNum;
ProductInfoBLL bll = new ProductInfoBLL();
int pageCount = 0; //表示总页数
int recordCount = 0; //表示数据的总条数
this.IntroductionList.DataSource = bll.GetBindList(5, pageNum, ref pageCount, ref recordCount);
this.IntroductionList.DataBind();
pCount = pageCount;
rCount = recordCount;
} /// <summary>
/// 对简介内容进行截取
/// </summary>
/// <param name="txt"></param>
/// <param name="length"></param>
/// <returns></returns>
protected string CutString(string txt, int length)
{
if (txt.Length > length)
{
return txt.Substring(0, length) + "....";
}
else
{
return txt;
}
}
/// <summary>
/// 上一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void prePage_Click(object sender, EventArgs e)
{
int prePage = Convert.ToInt32(Request.Form["cPage"]) - 1;
if (prePage < 1)
{
prePage = 1;
}
BindDataList(prePage);
}
/// <summary>
/// 下一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void nextPage_Click(object sender, EventArgs e)
{
int prePage = Convert.ToInt32(Request.Form["cPage"]) + 1;
pCount = Convert.ToInt32(Request.Form["pCount"]);
if (prePage > pCount)
{
prePage = pCount;
}
BindDataList(prePage);
}
/// <summary>
/// 跳转到第一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
BindDataList(1);
} /// <summary>
/// 跳转到最后一页
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button2_Click(object sender, EventArgs e)
{
BindDataList(Convert.ToInt32(Request.Form["pCount"]));
}
}
}
* Description:
* 2000高效性能分页
* Example:
* SuperPaging @TableName='表名',@Orderfld='排序列名'
*/
CREATE PROCEDURE SupesoftPage
(
@TableName nvarchar(50), -- 表名
@ReturnFields nvarchar(2000) = '*', -- 需要返回的列
@PageSize int = 10, -- 每页记录数
@PageIndex int = 1, -- 当前页码
@Where nvarchar(2000) = '', -- 查询条件
@Orderfld nvarchar(2000), -- 排序字段名 最好为唯一主键
@OrderType int = 1 -- 排序类型 1:降序 其它为升序)
AS
DECLARE @TotalRecord int
DECLARE @TotalPage int
DECLARE @CurrentPageSize int
DECLARE @TotalRecordForPageIndex int
DECLARE @OrderBy nvarchar(255)
DECLARE @CutOrderBy nvarchar(255)if @OrderType = 1
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc '
set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
END
else
BEGIN
set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' asc,') + ' asc '
set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '
END
-- 记录总数
declare @countSql nvarchar(4000)
set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where
execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord outSET @TotalPage=(@TotalRecord-1)/@PageSize+1
SET @CurrentPageSize=@PageSize
IF(@TotalPage=@PageIndex)
BEGIN
SET @CurrentPageSize=@TotalRecord%@PageSize
IF(@CurrentPageSize=0)
SET @CurrentPageSize=@PageSize
END
-- 返回记录
set @TotalRecordForPageIndex=@PageIndex*@PageSize
exec('SELECT * FROM
(SELECT TOP '+@CurrentPageSize+' * FROM
(SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+'
FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2
'+@CutOrderBy+') TB3
'+@OrderBy)
-- 返回总页数和总记录数
SELECT @TotalPage as PageCount,@TotalRecord as RecordCount
GO
2000我都用这个分页存储过程分页面。
int pageSize = 0; //每页显示行数
int nMax = 0; //总记录数
int pageCount = 0; //页数=总记录数/每页显示行数
int pageCurrent = 0; //当前页号
int nCurrent = 0; //当前记录行
DataSet ds = new DataSet();
DataTable dtInfo = new DataTable();
//2、在窗体载入事件中,从数据源读取记录到DataTable中:
string strConn = "SERVER=127.0.0.1;DATABASE=NORTHWIND;UID=SA;PWD=ULTRATEL"; //数据库连接字符串
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
string strSql = "SELECT * FROM CUSTOMERS";
SqlDataAdapter sda = new SqlDataAdapter(strSql,conn);
sda.Fill(ds,"ds");
conn.Close();
dtInfo = ds.Tables[0];
InitDataSet();
//3、用当前页面数据填充DataGridView
private void InitDataSet()
{
pageSize = 20; //设置页面行数
nMax = dtInfo.Rows.Count;
pageCount=(nMax/pageSize); //计算出总页数
if ((nMax % pageSize) > 0) pageCount++;
pageCurrent = 1; //当前页数从1开始
nCurrent = 0; //当前记录数从0开始
LoadData();
}
private void LoadData()
{
int nStartPos = 0; //当前页面开始记录行
int nEndPos = 0; //当前页面结束记录行
DataTable dtTemp = dtInfo.Clone(); //克隆DataTable结构框架
if (pageCurrent == pageCount)
{
nEndPos = nMax;
}
else
{
nEndPos = pageSize * pageCurrent;
}
nStartPos = nCurrent;
lblPageCount.Text = pageCount.ToString();
txtCurrentPage.Text = Convert.ToString(pageCurrent);
//从元数据源复制记录行
for (int i = nStartPos; i < nEndPos; i++)
{
dtTemp.ImportRow(dtInfo.Rows[i]);
nCurrent++;
}
bdsInfo.DataSource = dtTemp;
bdnInfo.BindingSource = bdsInfo;
dgvInfo.DataSource = bdsInfo;
}
// 4、菜单响应事件:
private void bdnInfo_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
{
if (e.ClickedItem.Text == "关闭")
{
this.Close();
}
if (e.ClickedItem.Text == "上一页")
{
pageCurrent--;
if (pageCurrent <= 0)
{
MessageBox.Show("已经是第一页,请点击“下一页”查看!");
return;
}
else
{
nCurrent = pageSize * (pageCurrent - 1);
}
LoadData();
}
if (e.ClickedItem.Text == "下一页")
{
pageCurrent++;
if (pageCurrent > pageCount)
{
MessageBox.Show("已经是最后一页,请点击“上一页”查看!");
return;
}
else
{
nCurrent=pageSize*(pageCurrent-1);
}
LoadData();
}
}