参考周健老大的分页存储过程: CREATE PROC sp_PageView @tbname sysname, --要分页显示的表名 @FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC --用于指定排序顺序 @Where nvarchar(1000)='', --查询条件 @PageCount int OUTPUT --总页数 AS DECLARE @sql nvarchar(4000) SET NOCOUNT ON --检查对象是否有效 IF OBJECT_ID(@tbname) IS NULL BEGIN RAISERROR(N'对象"%s"不存在',1,16,@tbname) RETURN END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0 BEGIN RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname) RETURN END--分页字段检查 IF ISNULL(@FieldKey,N'')='' BEGIN RAISERROR(N'分页处理需要主键(或者惟一键)',1,16) RETURN END--其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*' IF ISNULL(@FieldOrder,N'')=N'' SET @FieldOrder=N'' ELSE SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder) IF ISNULL(@Where,N'')=N'' SET @Where=N'' ELSE SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN SET @sql=N'SELECT @PageCount=COUNT(*)' +N' FROM '+@tbname +N' '+@Where EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT SET @PageCount=(@PageCount+@PageSize-1)/@PageSize END--计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 IF @PageCurrent=1 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' '+@Where +N' '+@FieldOrder) ELSE BEGIN SELECT @PageCurrent=@TopN1, @sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN +N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey +N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname +N' '+@Where +N' '+@FieldOrder SET ROWCOUNT @PageCurrent EXEC sp_executesql @sql, N'@n int,@s nvarchar(4000) OUTPUT', @PageCurrent,@sql OUTPUT SET ROWCOUNT 0 IF @sql=N'' EXEC(N'SELECT TOP 0' +N' '+@FieldShow +N' FROM '+@tbname) ELSE BEGIN SET @sql=STUFF(@sql,1,1,N'') --执行查询 EXEC(N'SELECT TOP '+@TopN +N' '+@FieldShow +N' FROM '+@tbname +N' WHERE '+@FieldKey +N' IN('+@sql +N') '+@FieldOrder) END END GO
using System.Data; partial class TrainRecNavigation : System.Web.UI.Page { public string strLinkManitCrsCat; public string strlinkQueryCrsCat; public string strlink2; public string strClassNum = ""; public string strCourseNum = ""; string strfac = ""; string strdept = ""; string strApproDate = ""; string strApproDate2 = ""; string strApproStatus = ""; string strTutorID = ""; string strpage = ""; int coursenum; int classnum; int PageCount; int RecCount; int CurrentPage; int Current; int Pages; int JumpPage; int PageSize = 15; private void // ERROR: Handles clauses are not supported in C# Page_Load(object sender, System.EventArgs e) { if (string.IsNullOrEmpty(Session("UserID"))) { Response.Write("<script>alert('您尚未登錄或登錄超時,請重新登錄!');parent.location.href='login.aspx';</script>>"); } strClassNum = Server.UrlDecode(Request.QueryString("strClassNum")) + ""; strCourseNum = Server.UrlDecode(Request.QueryString("strCourseNum")) + ""; strfac = Server.UrlDecode(Request.QueryString("strfac")) + ""; strdept = Server.UrlDecode(Request.QueryString("strdept")) + ""; strApproDate = Server.UrlDecode(Request.QueryString("strApproDate")) + ""; strApproDate2 = Server.UrlDecode(Request.QueryString("strApproDate2")) + ""; strApproStatus = Server.UrlDecode(Request.QueryString("strApproStatus")) + ""; strTutorID = Server.UrlDecode(Request.QueryString("strTutorID")) + ""; strpage = Request.QueryString("page") + ""; if (!Page.IsPostBack) { //----------------------------------------- RecCount = Calc(); //計算總記錄 PageCount = RecCount / PageSize + OverPage(); //計算總頁數 ViewState("PageCounts") = RecCount / PageSize - ModPage(); if (!string.IsNullOrEmpty(strpage)) { //設置當前頁為返回頁 ViewState("PageIndex") = (int)strpage; } else { //設置當前頁為1 ViewState("PageIndex") = 1; Session("CurPage") = 1; }
if (strGetDataFlag == "1") StringBuilder strSQL2 = new StringBuilder("select * from (select a.rec_id,a.cls_num,a.emp_id,a.taken_hr,a.exam_result,a.finasst_per,ROW_NUMBER() OVER(ORDER BY a.rec_id) AS number from classes a )T1 where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)") if (strClassNum.Trim.Length > 0) { strSQL2.Append(" And a.cls_num like '%" + strClassNum + "%'"); } if (strCourseNum.Trim.Length > 0) { strSQL2.Append(" And a.crs_num like '%" + strCourseNum + "%'"); } if (strfac.Trim.Length > 0) { strSQL2.Append(" And a.fac_num like '%" + strfac + "%'"); } if (strdept.Trim.Length > 0) { strSQL2.Append(" And a.dept_num like '%" + strdept + "%'"); } if (strApproStatus.Trim.Length > 0) { strSQL2.Append(" And a.appro_status like '%" + strApproStatus + "%'"); } if (strTutorID.Trim.Length > 0) { strSQL2.Append(" And a.emp_id like '%" + strTutorID + "%'"); } if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length == 0) { strSQL2.Append(" And a.appro_date like '%" + strApproDate + "%' "); } if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length > 0) { strSQL2.Append(" And a.appro_date between '" + strApproDate + "' and '" + strApproDate2 + "' "); } // strSQL &= " order by a.cls_num desc" dsResult = clsDBProcess.GetDataSet(Session("Server"), Session("Database"), strSQL2.ToString); //這里可以改為自己的GetDataSet方法 Repeater1.DataSource = dsResult; //這里可以改為 gridview 或datalist Repeater1.DataBind(); LCurrentPage.Text = CurrentPage.ToString(); } //msgbox.Text = Pages.ToString() } catch (Exception ex) { msgbox.Text = (ex.ToString); } finally { dsResult = null; clsDBProcess = null; } }
select * from (select a.rec_id,a.cls_num,a.emp_id,a.taken_hr,a.exam_result,a.finasst_per,ROW_NUMBER() OVER(ORDER BY a.rec_id) AS number from classes a )T1 where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)") 你只要会用这个sql就知道怎么取数据了。
有几种写法 我说的都是与应用平台无关的,不使用rowcount之类的 如 1 select top 20 * from table1 where id not in (select 20 id from table1)2 先反排 40个,再正排20个select top 20 * from ( select 40 * from table 1 order by id desc ) order by id asc
select top 20 * from table1 where id not in (select top 20 id from table1) 请问,这个语句怎么和分页联系起来呢?看不懂~
top 20 中的 20就是分页
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) begin drop procedure [dbo].[SelectPage] end GO CREATE PROCEDURE [dbo].[SelectPage]-- Author: Yan Min -- Created: 18-June-2009@PageNumber int, @PageSize intAS DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize SET @PageUpperBound = @PageLowerBound + @PageSize + 1CREATE TABLE #PageIndex ( IndexID int IDENTITY (1, 1) NOT NULL, key nvarchar(20) )BEGININSERT INTO #PageIndex ( key )SELECT [key]
FROM [dbo].[Table]
ENDDECLARE @TotalRows int DECLARE @TotalPages int DECLARE @Remainder intSET @TotalRows = (SELECT Count(*) FROM #PageIndex) SET @TotalPages = @TotalRows / @PageSize SET @Remainder = @TotalRows % @PageSize IF @Remainder > 0 SET @TotalPages = @TotalPages + 1 SELECT *, 'TotalPages' = @TotalPages
FROM [dbo].[table] t1JOIN #PageIndex t2 ON t1.[key] = t2.[key] WHERE t2.IndexID > @PageLowerBound AND t2.IndexID < @PageUpperBound
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
--用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键(或者惟一键)',1,16)
RETURN
END--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize --第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
GO
{
public string strLinkManitCrsCat;
public string strlinkQueryCrsCat;
public string strlink2;
public string strClassNum = "";
public string strCourseNum = "";
string strfac = "";
string strdept = "";
string strApproDate = "";
string strApproDate2 = "";
string strApproStatus = "";
string strTutorID = "";
string strpage = "";
int coursenum;
int classnum;
int PageCount;
int RecCount;
int CurrentPage;
int Current;
int Pages;
int JumpPage;
int PageSize = 15;
private void // ERROR: Handles clauses are not supported in C# Page_Load(object sender, System.EventArgs e)
{
if (string.IsNullOrEmpty(Session("UserID"))) {
Response.Write("<script>alert('您尚未登錄或登錄超時,請重新登錄!');parent.location.href='login.aspx';</script>>");
}
strClassNum = Server.UrlDecode(Request.QueryString("strClassNum")) + "";
strCourseNum = Server.UrlDecode(Request.QueryString("strCourseNum")) + "";
strfac = Server.UrlDecode(Request.QueryString("strfac")) + "";
strdept = Server.UrlDecode(Request.QueryString("strdept")) + "";
strApproDate = Server.UrlDecode(Request.QueryString("strApproDate")) + "";
strApproDate2 = Server.UrlDecode(Request.QueryString("strApproDate2")) + "";
strApproStatus = Server.UrlDecode(Request.QueryString("strApproStatus")) + "";
strTutorID = Server.UrlDecode(Request.QueryString("strTutorID")) + "";
strpage = Request.QueryString("page") + "";
if (!Page.IsPostBack) {
//-----------------------------------------
RecCount = Calc();
//計算總記錄
PageCount = RecCount / PageSize + OverPage();
//計算總頁數
ViewState("PageCounts") = RecCount / PageSize - ModPage();
if (!string.IsNullOrEmpty(strpage)) {
//設置當前頁為返回頁
ViewState("PageIndex") = (int)strpage;
}
else {
//設置當前頁為1
ViewState("PageIndex") = 1;
Session("CurPage") = 1;
}
ViewState("JumpPages") = PageCount;
LPageCount.Text = PageCount.ToString();
LTotalCount.Text = RecCount.ToString();
if (RecCount <= PageSize) {
gotoPage.Enabled = false;
}
else {
gotoPage.Enabled = true;
}
//msgbox.Text = RecCount
//----------------------------------------
CreatePagedDataSource("");
}
strLinkManitCrsCat = GotoMaintPage();
strlinkQueryCrsCat = GotoQueryPage();
strlink2 = GotoMaintPage2();
}
#region "計算總行數"
public int OverPage()
{
//算余
int pages = 0;
if (RecCount % PageSize != 0) {
pages = 1;
}
else {
pages = 0;
}
return pages;
}
public int ModPage()
{
//算余
int pages = 0;
if (RecCount % PageSize == 0 && RecCount != 0) {
pages = 1;
}
else {
pages = 0;
}
return pages;
}
public int Calc()
{
//計算記錄總數
dbprocess.OledbProcess clsDBProcess = new dbprocess.OledbProcess();
DataSet dsResult = new DataSet();
//Dim a5 As String = Server.UrlDecode(Request.QueryString("a5"))
StringBuilder ass = new StringBuilder("Select count(cls_num) as co from TRAIN_REC a where 1=1 ");
if (strClassNum.Trim.Length > 0) {
ass.Append(" And a.cls_num like '%" + strClassNum + "%'");
}
if (strCourseNum.Trim.Length > 0) {
ass.Append(" And a.crs_num like '%" + strCourseNum + "%'");
}
if (strfac.Trim.Length > 0) {
ass.Append(" And a.fac_num like '%" + strfac + "%'");
}
if (strdept.Trim.Length > 0) {
ass.Append(" And a.dept_num like '%" + strdept + "%'");
}
if (strApproStatus.Trim.Length > 0) {
ass.Append(" And a.appro_status like '%" + strApproStatus + "%'");
}
if (strTutorID.Trim.Length > 0) {
ass.Append(" And a.emp_id like '%" + strTutorID + "%'");
}
if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length == 0) {
ass.Append(" And a.appro_date like '%" + strApproDate + "%' ");
}
if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length > 0) {
ass.Append(" And a.appro_date between '" + strApproDate + "' and '" + strApproDate2 + "' ");
}
string bss = ass.ToString();
int RecordCount = 0;
dsResult = clsDBProcess.GetDataSet(Session("Server"), Session("Database"), bss);
RecordCount = Int32.Parse(dsResult.Tables(0).Rows(0)(0));
return RecordCount;
msgbox.Text = RecordCount;
}
#endregion
#region "翻頁"
//下一頁
protected void // ERROR: Handles clauses are not supported in C# Nextpage_Click(object sender, System.EventArgs e)
{
CurrentPage = (int)ViewState("PageIndex");
Pages = (int)ViewState("PageCounts");
CurrentPage = CurrentPage + 1;
ViewState("PageIndex") = CurrentPage;
Session("CurPage") = CurrentPage;
CreatePagedDataSource("");
}
//上一頁
protected void // ERROR: Handles clauses are not supported in C# Prevpage_Click(object sender, System.EventArgs e)
{
CurrentPage = (int)ViewState("PageIndex");
Pages = (int)ViewState("PageCounts");
CurrentPage -= 1;
ViewState("PageIndex") = CurrentPage;
Session("CurPage") = CurrentPage;
CreatePagedDataSource("");
}
//最后一頁
protected void // ERROR: Handles clauses are not supported in C# Lastpage_Click(object sender, System.EventArgs e)
{
CurrentPage = (int)ViewState("PageIndex");
Pages = (int)ViewState("PageCounts");
CurrentPage = Pages;
ViewState("PageIndex") = CurrentPage;
Session("CurPage") = CurrentPage;
CreatePagedDataSource("");
}
//第一頁
protected void // ERROR: Handles clauses are not supported in C# Fistpage_Click(object sender, System.EventArgs e)
{
CurrentPage = (int)ViewState("PageIndex");
Pages = (int)ViewState("PageCounts");
CurrentPage = 1;
ViewState("PageIndex") = CurrentPage;
Session("CurPage") = CurrentPage;
CreatePagedDataSource("");
}
//轉到第幾頁
protected void // ERROR: Handles clauses are not supported in C# gotoPage_TextChanged(object sender, System.EventArgs e)
{
string asd = this.gotoPage.Text.Trim().ToString();
JumpPage = (int)ViewState("JumpPages");
if (string.IsNullOrEmpty(asd)) {
Alert("超出范围");
return;
}
if (Int32.Parse(gotoPage.Text) > JumpPage || Int32.Parse(gotoPage.Text) <= 0 || string.IsNullOrEmpty(asd)) {
Alert("超出范围");
return;
}
else {
int InputPage = Int32.Parse(gotoPage.Text.ToString());
ViewState("PageIndex") = InputPage;
Session("CurPage") = InputPage;
CreatePagedDataSource("");
}
}
}
#endregion
{
int intPage = 0;
string strMid = null;
string strSQL = "";
DataSet dsResult = new DataSet();
dbprocess.OledbProcess clsDBProcess = new dbprocess.OledbProcess();
string strMidName = null;
string strGetDataFlag = null;
CurrentPage = (int)ViewState("PageIndex");
Pages = (int)ViewState("PageCounts");
if (CurrentPage > 1) {
Fistpage.Enabled = true;
Prevpage.Enabled = true;
}
else {
Fistpage.Enabled = false;
Prevpage.Enabled = false;
}
if (CurrentPage == Pages + 1) {
Nextpage.Enabled = false;
Lastpage.Enabled = false;
}
else {
Nextpage.Enabled = true;
Lastpage.Enabled = true;
}
try {
strGetDataFlag = Request.QueryString("getdata") + "";
strMid = Request.QueryString("mid") + "";
strMidName = GetMenuName(Session("Server"), Session("Database"), strMid);
tb1.Rows(0).Cells(0).InnerHtml = " <b>" + strMidName + "</b>";
if (strGetDataFlag == "1")
StringBuilder strSQL2 = new StringBuilder("select * from (select a.rec_id,a.cls_num,a.emp_id,a.taken_hr,a.exam_result,a.finasst_per,ROW_NUMBER() OVER(ORDER BY a.rec_id) AS number from classes a )T1 where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)")
if (strClassNum.Trim.Length > 0) {
strSQL2.Append(" And a.cls_num like '%" + strClassNum + "%'");
}
if (strCourseNum.Trim.Length > 0) {
strSQL2.Append(" And a.crs_num like '%" + strCourseNum + "%'");
}
if (strfac.Trim.Length > 0) {
strSQL2.Append(" And a.fac_num like '%" + strfac + "%'");
}
if (strdept.Trim.Length > 0) {
strSQL2.Append(" And a.dept_num like '%" + strdept + "%'");
}
if (strApproStatus.Trim.Length > 0) {
strSQL2.Append(" And a.appro_status like '%" + strApproStatus + "%'");
}
if (strTutorID.Trim.Length > 0) {
strSQL2.Append(" And a.emp_id like '%" + strTutorID + "%'");
}
if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length == 0) {
strSQL2.Append(" And a.appro_date like '%" + strApproDate + "%' ");
}
if (strApproDate.Trim.Length > 0 & strApproDate2.Trim.Length > 0) {
strSQL2.Append(" And a.appro_date between '" + strApproDate + "' and '" + strApproDate2 + "' ");
}
// strSQL &= " order by a.cls_num desc"
dsResult = clsDBProcess.GetDataSet(Session("Server"), Session("Database"), strSQL2.ToString); //這里可以改為自己的GetDataSet方法
Repeater1.DataSource = dsResult; //這里可以改為 gridview 或datalist
Repeater1.DataBind();
LCurrentPage.Text = CurrentPage.ToString();
}
//msgbox.Text = Pages.ToString()
}
catch (Exception ex) {
msgbox.Text = (ex.ToString);
}
finally {
dsResult = null;
clsDBProcess = null;
}
}
你只要会用这个sql就知道怎么取数据了。
我说的都是与应用平台无关的,不使用rowcount之类的
如
1
select top 20 * from table1 where id not in (select 20 id from table1)2
先反排 40个,再正排20个select top 20 * from
(
select 40 * from table 1 order by id desc
)
order by id asc
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SelectPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
drop procedure [dbo].[SelectPage]
end
GO
CREATE PROCEDURE [dbo].[SelectPage]-- Author: Yan Min
-- Created: 18-June-2009@PageNumber int,
@PageSize intAS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = (@PageSize * @PageNumber) - @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize + 1CREATE TABLE #PageIndex
(
IndexID int IDENTITY (1, 1) NOT NULL,
key nvarchar(20)
)BEGININSERT INTO #PageIndex (
key
)SELECT
[key]
FROM
[dbo].[Table]
ENDDECLARE @TotalRows int
DECLARE @TotalPages int
DECLARE @Remainder intSET @TotalRows = (SELECT Count(*) FROM #PageIndex)
SET @TotalPages = @TotalRows / @PageSize
SET @Remainder = @TotalRows % @PageSize
IF @Remainder > 0
SET @TotalPages = @TotalPages + 1
SELECT
*,
'TotalPages' = @TotalPages
FROM
[dbo].[table] t1JOIN #PageIndex t2
ON
t1.[key] = t2.[key]
WHERE
t2.IndexID > @PageLowerBound
AND t2.IndexID < @PageUpperBound
ORDER BY t2.IndexID把里面的Table改成你要的表名,Key改成你的主键.
GridView中属性:
PageSize="15" AllowPaging="true" OnPageIndexChanging="grvData_PageIndexChanging"