分页加搜索sql语句

--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

 

1.此种方法是先查询出所需要的数据表格再添加序列号No（分页搜索时推荐使用这种）

select T.* from(

select ROW_NUMBER() over(order by XM desc) as No,A.* from (    　　　　//将A表加上序列号

select * from RZSH WHERE 1=1 sqlwhere) AS A

) AS T 

WHERE T.No>startIndex and T.No<endIndex

2.此种方法是将表格添加序列号再进行查询（此种弊端是sqlwhere搜索后的数据不是从1开始(因为已经在搜索前编过序号了)，若查询出的数据是12-15行的，而T.No>1 and T.No<10，则不能显示或显示不全）

select * from (

select * from (

select ROW_NUMBER() over(order by SBRQ DESC) as No,* from RZSH

) as a  WHERE 1=1  and XM like '%飞%'

) as T 

WHERE T.No>0 and T.No<11

 

 

前台页面

                                        <webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首页" LastPageText="尾页"
                                            ShowCustomInfoSection="Left" CustomInfoClass="fengyebox_left" PageSize="10" AlwaysShow="true"
                                            OnPageChanged="AspNetPager1_PageChanged" CustomInfoHTML="数据共%RecordCount%条，当前%CurrentPageIndex%/%PageCount%页"
                                            NextPageText="下一页" PageIndexBoxType="DropDownList" PrevPageText="上一页" ShowPageIndexBox="Always"
                                            SubmitButtonText="Go" TextAfterPageIndexBox="页" TextBeforePageIndexBox="转到" CurrentPageButtonClass="fengye_btn1"
                                            FirstLastButtonsClass="fengye_sxyy" MoreButtonsClass="fengye_btn3" PagingButtonsClass="fengye_btn2"
                                            PrevNextButtonsClass="fengye_sxyy">
                                        </webdiyer:AspNetPager>

后台

        //当定数据和分页
        public void BindData()
        {
            string sqlwhere = "";
            if (TxtName.Text.Trim() != "")
            {
                sqlwhere += " and XM like '%" + TxtName.Text.Trim() + "%'";
            }
            if (TxtAddress.Text.Trim() != "")
            {
                sqlwhere += " and FYDZ like '%" + TxtAddress.Text.Trim() + "%' or FJH like '%" + TxtAddress.Text.Trim() + "%'";
            }
            if (TxtDate.Text.Trim() != "")
            {
                sqlwhere += " and SBRQ>'" + TxtDate.Text.Trim() + "'";
            }
            //int index = Convert.ToInt32(ViewState["index"].ToString());
            int index = AspNetPager1.CurrentPageIndex;
            DataTable dt = sbsh.GetView(sqlwhere).Tables[0];
            DataSet ds = sbsh.GetViewByPage(sqlwhere, "SBRQ DESC", (index - 1) * 10, (index - 1) * 10 + 11);
            AspNetPager1.RecordCount = dt.Rows.Count;
            AspNetPager1.PageSize = 10;
            Repeater1.DataSource = ds.Tables[0];
            Repeater1.DataBind();
            for (int i = 0; i < this.Repeater1.Items.Count; i++)
            {
                Button btn = (Button)this.Repeater1.Items[i].FindControl("BtnSH");
                HiddenField hfd = (HiddenField)this.Repeater1.Items[i].FindControl("hfd");
                if (hfd.Value == "0230000001")
                {
                    btn.Enabled = false;
                }
            }
        }
        //当前页索引改变事件
        protected void AspNetPager1_PageChanged(object sender, EventArgs e)
        {
            //ViewState["index"] = AspNetPager1.CurrentPageIndex;
            BindData();
        }
        //绑定审核状态
        public string BindSHZT(string shzt)
        {
            DM_DMBManager dmb = new DM_DMBManager();
            DM_DMB dmbmodel= dmb.GetModel(shzt);
            if (dmbmodel != null)
            {
                return dmbmodel.DMMC;
            }
            else
            {
                return "";
            }
        }

        protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e)
        {
            if (e.CommandName == "edit")
            {
                string date = DateTime.Now.ToShortDateString();
                string time = DateTime.Now.ToShortTimeString ();
                string datetime = date + " " + time;//审核时间
                string sbbh = e.CommandArgument.ToString();
                sbsh.UpdateSHZT(sbbh, datetime);
                BindData();
            }

        }
        //搜索
        protected void BtnSearch_Click(object sender, EventArgs e)
        {
            AspNetPager1.CurrentPageIndex = 1;
            BindData();
        }