亲，您未登录哦！ 登录 | 注册
首页  |  社区  |  工具箱  |  代码库  |  博客  |  招聘  |  文章  |  新闻  |  下载  |  读书
回 帖 发 新 帖 刷新版面

主题：联合查询＋分页

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

<%@language=VBScript%>
<%Option Explicit%>
<!--#include file="../Lib/Config.asp"-->
<!--#include file="../Lib/FormElement.asp"-->
<!--#include file="../Lib/StringFunctions.asp"-->
<%
    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>&nbsp;</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个回复）

沙发

jim.ma [专家分：1100] 发布于 2003-12-18 19:12:00

我跟个通用分页 :) 大家修改一点都可以用. 简单修改可以用了。

<%
'SQL语句自己设置
sql="select * from table order by id desc"

    Set rs= Server.CreateObject("ADODB.Recordset")

    rs.open sql,conn,1,1

      if rs.eof and rs.bof then
               response.write "<p align='center'> 还 没 有 任 何 文 章 </p>"
       else
              totalPut=rs.recordcount
              if currentpage<1 then
                  currentpage=1
              end if
              if (currentpage-1)*MaxPerPage>totalput then
               if (totalPut mod MaxPerPage)=0 then
                     currentpage= totalPut \ MaxPerPage
              else
                      currentpage= totalPut \ MaxPerPage + 1
               end if

              end if
               if currentPage=1 then
                   showpage totalput,MaxPerPage,"manage.asp"
                    showContent
                    showpage totalput,MaxPerPage,"manage.asp"
               else
                  if (currentPage-1)*MaxPerPage<totalPut then
                        rs.move  (currentPage-1)*MaxPerPage

                       showpage totalput,MaxPerPage,"manage.asp"
'文件名字简单修改
                        showContent
                         showpage totalput,MaxPerPage,"manage.asp"
                else
                    currentPage=1
                       showpage totalput,MaxPerPage,"manage.asp"
                       showContent
                       showpage totalput,MaxPerPage,"manage.asp"
                  end if
           end if
       rs.close
       end if
            
       set rs=nothing  
       conn.close
       set conn=nothing
%>
<%
'下面是显示内容部分
       sub showContent
           dim i
       i=0
%>
<!--你的内容显示部分-->
<%
    i=i+1
          if i>=MaxPerPage then exit do
          rs.movenext
    loop
end sub
%>
<%
'页码

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'>&nbsp;"
  if CurrentPage<2 then
    response.write "<font color='#000080'>首页 上一页</font>&nbsp;"
  else
    response.write "<a href="&filename&"?page=1&txtitle="&title&">首页</a>&nbsp;"
    response.write "<a href="&filename&"?page="&CurrentPage-1&"&txtitle="&title&">上一页</a>&nbsp;"
  end if
  if n-currentpage<1 then
    response.write "<font color='#000080'>下一页 尾页</font>"
  else
    response.write "<a href="&filename&"?page="&(CurrentPage+1)&"&txtitle="&title&">"
    response.write "下一页</a> <a href="&filename&"?page="&n&"&txtitle="&title&">尾页</a>"
  end if
   response.write "<font color='#000080'>&nbsp;页次：</font><strong><font color=red>"&CurrentPage&"</font><font color='#000080'>/"&n&"</strong>页</font> "
    response.write "<font color='#000080'>&nbsp;共<b>"&totalnumber&"</b>个文档 <b>"&maxperpage&"</b>个文档/页</font> "
       
end function
%>

板凳

快乐至上 [专家分：730] 发布于 2003-12-21 21:22:00

好長亞﹐看得我頭發暈﹐腦發脹﹐眼冒金花﹐不過還是謝謝你們﹐

3 楼

马桶超人 [专家分：60] 发布于 2003-12-22 10:20:00

经典

4 楼

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

TO:马桶超人
你那天还看明白了吗？

5 楼

马桶超人 [专家分：60] 发布于 2003-12-22 16:17:00

太君：我地十成明白了两成

6 楼

driver_pro [专家分：10] 发布于 2004-03-03 18:11:00

我也发个分页的，好像我的这个短些，假设当前页面为“#.asp”:
<%
page=request("page")
if page="" then page=session("page")
if page="" then page=1
page=cint(page)
session("page")=page
fwe=*                                 'fwe为每页象要显示的记录数
a=1
'数据库连接自己设置
set rs=server.createobject("adodb.recordset")
sql="select * from 数据库 order by id desc"
rs.open sql,conna,1,3
if not rs.eof then
  dd1=rs.recordcount
  if page>int(dd1/fwe)+1 then page=1
    rs.pagesize=fwe
    rs.absolutepage=page
  end if
  sub pp()
%>
    <table cellSpacing="0" cellPadding="0" width="100%" border="0">
      <tr>
        <td align="right">
          <font color="#333333"> [<a href="#.asp?page=1">首页</a>] [
    <%if page>1 then%>
                  <a href="#.asp?page=<%=page-1%>">上一页</a>
    <%else%>
        上一页
    <%end if%>
        ] [
    <%if fwe*page<dd1 then%>
                  <a href="#.asp?page=<%=page+1%>">下一页</a>
    <%else%>
        下一页
    <%end if%>
        ] [
         <a href="#.asp?page=<%if (dd1 mod fwe)=0 then%><%=dd1/fwe%><%else%><%=int(dd1/fwe)+1%><%end if%>">尾页</a>]<b> 共
    <%if (dd1 mod fwe)=0 then%>
        <%=dd1/fwe%>
    <%else%>
        <%=int(dd1/fwe)+1%>
    <%end if%>
        页</b></font>
      </td>                                  
    </tr>
    </table>
<%end sub%>

7 楼

huye [专家分：0] 发布于 2004-03-04 21:00:00

简单的联合查询并不难做，但是多关键字的查询怎么实现，不知道各位有什么高见，如有不妨贴出来大家分享一下。

8 楼

绝对零度 [专家分：820] 发布于 2004-03-05 08:41:00

我想是一样的吧，用INNER JOIN连接

9 楼

dragonya [专家分：80] 发布于 2004-04-12 12:05:00

我也贴一个。

<table width="600" align="center" cellpadding="2" cellspacing="1" class=border>
  <tr class=title>
    <td>用户名</td>
    <td width="15%"><div align="center">管理选项</div></td>
  </tr>
  <%
set rs=server.createobject("adodb.recordset")
sql="select * from admin order by id desc"
rs.open sql,conn,1
          
rs.pagesize=20 '每页显示多少条
pagecount=rs.pagecount '总页数
          
if rs.bof and rs.eof then
response.write ""
else
                     
page=clng(request.querystring("page"))
if page<1 then page=1
if page>rs.pagecount then page=rs.pagecount
rs.absolutepage=page  '跳到多少页
  
for i=1 to rs.pagesize
%>
  <tr valign="middle" class=tdbg>
    <td>
      <% =rs("username")%>
    </td>
    <td><div align="center"><a href="del.asp?id=<%=Rs("id")%>">删除</a></div></td>
  </tr>
  <%
rs.movenext
if rs.eof then exit for
next
end if
%>
  <tr valign="middle" class=tdbg>
    <td colspan="3"> <div align="center"></div>
      <div align="center">
        <% set tj=conn.execute("Select count(*) as tj1 From admin ") %>
        共有〖
        <% =tj("tj1") %>
        〗条记录&nbsp;&nbsp;&nbsp;&nbsp;
        <%
    r=8
     
    '总页数
    zys=round(clng(rs.pagecount)/r+0.5)
    
    '当前页数
    dqys=request.QueryString("dqys")
    
    '开始页
    ksy=request.QueryString("ksy")
    
    if dqys="" then dqys=1
    if ksy="" then ksy=1

    js=clng(dqys) * r '一个FOR语句结束
    if js > clng(rs.pagecount) then js=clng(rs.pagecount)
    
    newksy= clng(dqys)*r-(r-1) '一个FOR语句开始
        
if clng(dqys) >1 then response.write "<a href=?dqys="& dqys-1 &">" &"上一页" &"</a>"

      for t=newksy to js
         response.write "<a href=?page=" &t&"&dqys="&dqys&">" &t& "</a>&nbsp;"
      next
      

if cdbl(dqys)< zys then response.write "<a href=?dqys="& dqys+1 &">" &"下一页" &"</a>"

    %>
      </div></td>
  </tr>
</table>

10 楼

yongweb [专家分：30] 发布于 2004-04-19 13:49:00

http://ygbbs.y365.com

ASP技术学习论坛,有兴趣到这里来看看,谢谢你的支持,里面有很多学习ASP技术的文章

本论坛正在招聘ASP技术方面的版主，欢迎你们来招聘！

我来回复

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

程序员工具箱 new

  • 文本转表格
  • 在线程序日志
  • 安卓APK权限检测
  • CSS代码美化压缩
  • 随机字符串生成
  • JavaScript库CDN服务
  • 占位图片生成
  • 自选股票查询
  • 在线抽奖
  • 身份证归属地查询
  • 更多工具

    • 代码片段

  • 模拟键盘（不可按）
  • 碰撞的唐三
  • 精彩奥运热点赛事的实时采集
  • 获取天气动态信息
  • 文件查找
  • python大数据抓取京东电脑销量数据
  • 爬虫技术不仅仅支持数据采集
  • 抓取脉脉APP上的职业信息
  • 爬虫中多线程的运用
  • 四则运算以及一元二次方程
  • 欢迎来共享你的NB代码

    • 本版新帖

  • 送段代码给大家
  • 禁用浏览器后退按钮
  • 各位？在ASP中什么样编写上传图形的程序？
  • 一些网页代码奉献
  • 服务器完全安装手册［绝对经典］
  • 小问题，只要2分钟就可。请教数据库排序的三种方式哪种好。
  • [原创]如何在自己的电脑上建立Web服务器。
  • 总结对于主页调用TOP10的一些经验.
  • 用ADODB.Stream做上传下载
  • [转载]ASP技巧28则(权威版)
  • [转载]数据库查询语言
  • 和大家一起研究探讨一个问题！！ 
  • 请问Replace函数怎么用啊？各位高手，可否举例说明？
  • 圪圪请进，有事请教～希望您能帮忙
  • 给大家收集了一些很实用的参考手册下载！（新）