• 分页加搜索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();
            }
    
  • 相关阅读:
    Python数据可视化的四种简易方法
    阿里如何将“高峰前扩容、高峰后缩容”的梦想照进现实?
    支付宝客户端架构解析:Android 容器化框架初探
    阿里工业互联网平台“思考”:一场从0到1的蜕变
    关于Flutter初始化流程,我必须告诉你的是...
    类在什么时候加载和初始化
    instanceof 与isAssignableFrom
    Java注释@interface的用法【转】
    网站过滤器Filter
    正则表达式
  • 原文地址:https://www.cnblogs.com/li-fei/p/3417079.html
Copyright © 2020-2023  润新知