联合查询＋分页 — 编程爱好者社区

主题：联合查询＋分页

绝对零度 [专家分：820] 发布于 2003-12-18 13:42:00

<%@language=VBScript%>
<%Option Explicit%>



<%
    Dim conMIS
    Set conMIS = Server.CreateObject("ADODB.Connection")
    conMIS.Open g_strDbConnection

    Dim rsList, sqlSn, PageNo, intPageNo, intPageSize, i, strProxyName, strSchoolName, strCourseName, strTeacherName, strClientContact, strSchoolProvince, dtReceiveMoneyTime, lngUserId

    Dim ProxyName, SchoolName, CourseName, TeacherName, ClientContact, SchoolProvince, ReceiveMoneyTime, UserId

        intPageNo = Request.QueryString ("PageNo")
        strProxyName = Request.QueryString("ProxyName")
        strSchoolName = Request.QueryString ("SchoolName")
        strCourseName = Request.QueryString ("CourseName")
        strTeacherName = Request.QueryString ("TeacherName")
        strClientContact = Request.QueryString ("ClientContact")
        strSchoolProvince = Request.QueryString ("SchoolProvince")
        dtReceiveMoneyTime = Request.QueryString ("ReceiveMoneyTime")
        lngUserId = Clng(Request.QueryString ("UserId"))

    Set rsList=server.CreateObject("ADODB.Recordset")

    sqlSn = "SELECT s.*, p.id AS ProvinceId, p.Name AS ProvinceName, l.id AS LogonId, l.UserName AS LogonUserName"
'    sqlSn = sqlSn & " FROM (Logon INNER JOIN (CodeProvince INNER JOIN Sn ON CodeProvince.id=Sn.SchoolProvince) ON Logon.id=Sn.Userid)"
    sqlSn = sqlSn & " FROM ((Sn s INNER JOIN Logon l ON s.UserId = l.Id)"'Sn为主表联合查询表CodeProvince和表Logon
    sqlSn = sqlSn & "              INNER JOIN CodeProvince p ON s.SchoolProvince = p.Id)"
    sqlSn = sqlSn & " WHERE 1 = 1"

    If ( strProxyName <> "" ) Then sqlSn = sqlSn & " AND (s.ProxyName LIKE '%" & strProxyName & "%')"'如果strProxyName值不为空则执行strProxyName值的查询
    If ( strSchoolName <> "" ) Then sqlSn = sqlSn & " AND (s.SchoolName LIKE '%" & strSchoolName & "%')"
    If ( strCourseName <> "" ) Then sqlSn = sqlSn & " AND (s.CourseName LIKE '%" & strCourseName & "%')"
    If ( strTeacherName <> "" ) Then sqlSn = sqlSn & " AND (s.TeacherName LIKE '%" & strTeacherName & "%')"
    If ( strSchoolProvince > 0 ) Then sqlSn = sqlSn & " AND (s.SchoolProvince =" & strSchoolProvince & ")"
    If ( dtReceiveMoneyTime <> "" ) Then sqlSn = sqlSn & " AND (DateDiff('d', s.ReceiveMoneyTime, '" & CDate(dtReceiveMoneyTime) & "') = 0)"
    If ( lngUserId > 0 ) Then sqlSn = sqlSn & " AND (l.Id = " & lngUserId & ")"

    sqlSn = sqlSn & " ORDER BY s.Id DESC"

    If intPageNo = "" Then'如果当前页数为空的话
        PageNo=1'则当前页数为1
    Else
        PageNo=intPageNo'否则为Request.QueryString ("PageNo")
    End If
    rsList.Open sqlSn, conMIS, 3, 1

    intPageSize = PAGE_SIZE'一页显示10条记录，常数

%>
<html>
<head>
    <link href="../Css/css.css" rel="stylesheet" type="text/css">
    <meta http-equiv="Content-Type" content="text/html; charset=gb2312">
</head>
    <body bgcolor="#e1e1e1" background="../Images/Background.gif" topmargin="0">
        <form action="SnList.asp" name="list" method="get">
            <div align="center">
                <br>
                <table rules="rows" width="100%" border="1" align="center" cellpadding="3" cellspacing="0" bordercolor="#cccccc" bordercolorlight="#666666" bordercolordark="#ffffff">
                    <tr class="text">
                        <td rowspan="3"><div align="center">选择搜索内容
                            </div>
                        </td>
                        <td><div align="left">
                                产品版本 <input name="ProxyName" type="text" class="input" id="ProxyName">
                            </div>
                        </td>
                        <td><div align="left">教师名称 <input name="TeacherName" type="text" class="input" id="TeacherName">
                            </div>
                        </td>
                        <td><div align="left">收到汇款的时间 <input name="ReceiveMoneyTime" type="text" class="input" id="ReceiveMoneyTime3">
                            </div>
                        </td>
                    </tr>
                    <tr class="text">
                        <td><div align="left">
                                学校名称 <input name="SchoolName" type="text" class="input" id="SchoolName">
                            </div>
                        </td>
                        <td><div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">
                            </div>
                            <div align="left">经手人
                                <%=g_htmlTableToSelect(conMIS, "SELECT Id, UserName FROM LogOn WHERE [Level] = 1 ORDER BY UserName ASC", "UserId", -1, "请选择经手人...")%>
                            </div>
                        </td>
                        <td> </td>
                    </tr>
                    <tr class="text">
                        <td>
                            课程名称 <input name="CourseName" type="text" class="input" id="CourseName"></td>
                        <td>省份
                            <%=g_htmlTableToSelect(conMIS, "SELECT Id, Name FROM CodeProvince ORDER BY Id ASC", "SchoolProvince", -1, "请选择省份...")%>
                        </td>
                        <td><div align="center">
                                <input type="image" src="../Images/Search.gif" name="Submit">
                            </div>
                        </td>
                    </tr>
                </table>
                <br>
                <%
    If (Not rsList.EOF) Then
        rsList.PageSize = intPageSize'规定一页显示10条记录
        rsList.CacheSize = intPageSize
        rsList.AbsolutePage = PageNo'显示当前第几页
%>

    <table width="100%" frame=box border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#cccccc" bordercolorlight="#666666" bordercolordark="#ffffff">
      <tr bgcolor="#cccccc" class="text">
        <td> <div align="center">产品版本</div></td>
        <td> <div align="center">学校名称</div></td>
        <td> <div align="center">课程名称</div></td>
        <td> <div align="center">教师名称</div></td>
        <td> <div align="center">联系人</div></td>
        <td> <div align="center">省份</div></td>
        <td> <div align="center">收到汇款的时间</div></td>
        <td> <div align="center">经手人</div></td>
        <td><div align="center">详情</div></td>
      </tr>
            <%
            i = 0
            DO while(Not rsList.EOF) and ( i < intPageSize)
            %>
      <tr class="text">
        <td height="20"> <div align="center">
            <% Call ShowListColor(strProxyName, "ProxyName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strSchoolName, "SchoolName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strCourseName, "CourseName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strTeacherName, "TeacherName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(strClientContact, "ClientContact") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor2(strSchoolProvince, "ProvinceName") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor(dtReceiveMoneyTime, "ReceiveMoneyTime") %>
          </div></td>
        <td><div align="center">
            <% Call ShowListColor2(lngUserId, "LogonUserName") %>
          </div></td>
        <td><div align="center"><a href =ShowSn.asp?ShowSnid=<% =rsList("id") %>>查看</a></div></td>
      </tr>
      <%
            i = i + 1
            rsList.MoveNext
        Loop
    Else
        Response.Redirect("searcherror.asp")
    End IF


%>
    </table>
            </div>
        </form>
        <table width="100%" border="0">
            <tr>
                <td align="right">
                    <font class="text">首页
                        <%
Response.Write("|")
    For i=1 To rsList.PageCount
            If strSchoolProvince > 0 Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&SchoolProvince="&strSchoolProvince&">"&cstr(i)&"</a>")
            ElseIf dtReceiveMoneyTime <> "" Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&ReceiveMoneyTime="&dtReceiveMoneyTime&">"&cstr(i)&"</a>")
            ElseIf lngUserId > 0 Then
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName="&strProxyName&"&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&"&UserId="&lngUserId&">"&cstr(i)&"</a>")
            Else
                Response.Write("<a href=SnList.asp?PageNo=" & i & "&ProxyName=" & strProxyName & "&SchoolName="&strSchoolName&"&CourseName="&strCourseName&"&TeacherName="&strTeacherName&"&ClientContact="&strClientContact&">"&cstr(i)&"</a>")
            End If
        If i<>rsList.PageCount Then
            Response.Write("|")
        End If
    Next
Response.Write("|")
%>
                        尾页</font>
                </td>
            </tr>
        </table>
    </body>
</html>

本帖地址： http://bbs.pfan.cn/post/25263.html

回复列表（共59个回复）

41 楼

白痴男 [专家分：170] 发布于 2006-11-21 20:04:00

if (currentpage-1)*MaxPerPage>totalput

这句可能实现吗?搞不明白

42 楼

eeehouse [专家分：0] 发布于 2007-01-06 11:46:00

有不耗资源的吗

43 楼

初拥创始者 [专家分：540] 发布于 2007-02-12 22:12:00

我发誓我以前没有看到这个帖子，最近才发现的~~~

44 楼

lanlan123 [专家分：0] 发布于 2007-02-18 12:32:00

<%
set rs=server.CreateObject("adodb.recordset")
sql_rs="select * from guest order by g_time desc"
rs.open sql_rs,conn,1,1

if rs.eof and rs.bof then
response.write("没有你要的内容")

else
'分页
dim page_no,page_size,page_total
page_size=5
if request("page_no")="" then
page_no=1
else
page_no=request("page_no")
end if
rs.pagesize=page_size
page_total=rs.pagecount
rs.absolutepage=page_no
end if
i=page_size
'输出的内容
省略
<div align="center" class="table2">共有留言:<%=rs.recordcount%>篇 当前为[<%=page_no%>]/[<%=page_total%>]页
    <% if page_no=1 then%>
    <%else%>

                        <a href="gsshow.asp?page_no=<%=page_no-1%>">上一页</a>
                        <%end if%>
                        <%if rs.pagecount-page_no<>0 then%>
                        <a href="gsshow.asp?page_no=<%=page_no+1%>">下一页</a>
    转到
    <input  name="page_no" type="text" id="page_no" alt="填于你想看到的页数" size="2" maxlength="10"
     value="<%=page_no%>">页
     <input name="submit" type="submit" value="GO"></div><%end if%></form>
     </td></tr></table></td></tr></table>
</body>
</html>
<%
    rs.close
    set rs=nothing
%>

45 楼

杀死进程 [专家分：510] 发布于 2007-03-19 16:22:00

最简单的分页，初学者适合

<%
    set cn=server.CreateObject("ADODB.Connection")
    cn.connectionstring="provider=sqloledb;uid=sa;pwd=;database=pubs"
    cn.open
    set rst=server.CreateObject("ADODB.RecordSet")
    with rst
        .ActiveConnection=cn
        .cursortype=adOpenDynamic
        .cursorlocation=adUseClient
        .locktype=adLockOptimistic
        .source="select * from authors"
        .open
    end with
    rst.pagesize=4
    dim record
    dim page

    page = CLng(request.QueryString("page"))
    if page=<1 then
        page=1
    end if
    if page>=rst.pagecount then
        page=rst.pagecount
    end if
    rst.AbsolutePage = page
     for record=1 to rst.pagesize
        response.Write(rst.fields("au_lname").value & "<br>")
        rst.movenext
        if rst.eof then
            exit for
        end if
    next
%>
<a href="testFenye.asp?page=1">第一页</a>
<a href="testFenye.asp?page=<%=(page+1)%>">下一页</a>
<a href="testFenye.asp?page=<%=(page-1)%>">上一页</a>
<a href="testFenye.asp?page=<%=rst.pagecount%>">最后一页</a>

46 楼

初拥创始者 [专家分：540] 发布于 2007-04-05 11:10:00

开始看这个顶一个~

47 楼

sldd [专家分：0] 发布于 2007-04-09 16:26:00

我用的就是这个,只是如何在查询时传递关键字呢

请参考我发的上一个帖子
http://www.programfan.com/club/showbbs.asp?id=225104

我编写了一个简单的设备浏览查询程序，进入页面后如下图所示，会分页显示所有设备，
如果在搜索框内填入关键字搜索后，如果得到的记录少于每页显示的个数18个，程序是没有问题的，

如果大于18个，只要点击下一页，则显示的记录立即变为所有记录的第二页

我的acton 是文件本身,根据提示,(那是因为你在翻页的时候，没把关键字这个参数传过去。)我重新修改了,可以翻页时,故障依旧

keyword=Trim(Request("key"))
...............
if trim(request.form("sss"))<>"" then
select case request.form("sss")
case "设备名称"
strSql ="select * from jhtdata where (设备名称 like '%"&keyword&"%') and (部门='"&myUpart&"') order by id Desc"
........................

function showpage(totalnumber,maxperpage,filename)
dim n
if totalnumber mod maxperpage=0 then
n= totalnumber \ maxperpage
else
n= totalnumber \ maxperpage+1
end if
response.write "<p align='center'> "
if CurrentPage<2 then
response.write "<font color='#000080'><a href="&filename&"?key="&keyword&"&page=1>首页</a> 上一页</font> "
else
response.write "<a href="&filename&"?key="&keyword&"&page=1>首页</a> "
response.write "<a href="&filename&"?key="&keyword&"&page="&CurrentPage-1&">上一页</a> "
end if
if n-currentpage<1 then
response.write "<font color='#000080'>下一页尾页</font>"
else
response.write "<a href="&filename&"?key="&keyword&"&page="&(CurrentPage+1)&">"
response.write "下一页</a> <a href="&filename&"?keyword="&keyword&"&page="&n&">尾页</a>"end if
response.write "<font color='#000080'> 页次：</font><strong><font color=red>"&CurrentPage&"</font><font color='#000080'>/"&n&"</strong>页</font> "
response.write "<font color='#000080'> 共<b>"&totalnumber&"</b>个记录 <b>"&maxperpage&"</b>个记录/页</font> "

response.write "<font color='#000080'>转到：</font><input type='text' name='page' size=4 maxlength=4 class=smallInput value="&Currentpage&"> "
response.write "<input class=buttonface type='submit' value='Go' name='cndok' >  "
end function

48 楼

xzf888 [专家分：0] 发布于 2007-05-03 10:00:00

这个联合查询是好, 可我等菜鸟看不懂, 如果连数据和程序一同提供参考就完美了, 这样我们可以看效果和实例了,希望提供下

49 楼

孤单远行 [专家分：0] 发布于 2007-05-03 10:28:00

我也跟一个分页程序：
<form Name=frmPage><div align=left>    第<%=objRS.AbsolutePage%>/<%=objRS.PageCount%>页
       共<%=objRS.RecordCount%>条记录


  <%If objRS.AbsolutePage>1 Then %>
             <a href=printrecord.asp?pageno=<%=objRS.AbsolutePage-1%>>上一页</a>
  <%Else%>
             上一页
  <%End If%>

  <%If objRS.AbsolutePage<objRS.PageCount Then%>
          <a href=printrecord.asp?pageno=<%=objRS.AbsolutePage+1%>>下一页</a>
  <%Else%>
          下一页
  <%End If%>

  <%If objRS.AbsolutePage>1 Then%>
          <a href=printrecord.asp?pageno=1>首页</a>
  <%Else%>
          首页
  <%End If%>

  <%If objRS.AbsolutePage<objRS.PageCount Then%>
          <a href=printrecord.asp?pageno=<%=objRS.PageCount%>>尾页</a>
  <%Else%>
          尾页
  <%End If%>

          
       <select name=page onchange="self.location.href=this.options                      [this.selectedIndex].value">
          <option selected>页数</option>
          <% For I=1 To objRS.PageCount %>
            <option value=printrecord.asp?pageno=<%=I%>>第<%=I%>页</option>
          <% Next %>
       </select>
        </div></form>

50 楼

8163 [专家分：10] 发布于 2007-05-27 07:19:00

怎么会如此垃圾的分页？

有没效率可言？

１０万记录下，这几个分页都不能用。

我来回复

您尚未登录，请登录后再回复。点此登录或注册

主题：联合查询＋分页

回复列表（共59个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

主题：联合查询＋分页

回复列表 （共59个回复）

我来回复

程序员工具箱 new

代码片段

本版新帖

回复列表（共59个回复）