sql联合查询
发布时间：2007-11-20 22:11:00 作者：admin 阅读：1103次数据库
mydata.mdb
表cart 字段 id userid bookname pressid datetime
表press 字段 id pressname address
表userinfo 字段 id username age datetime
index.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>无标题文档</title>
</head>
<%
Function sql_conn()
On Error Resume Next
dim Conn,Strconn
set Conn=server.createobject("adodb.connection")
Strconn = "Provider = Sqloledb; User ID = sa; Password = sa; Initial Catalog = victor; Data Source = (local);"
Conn.open Strconn
set sql_conn=Conn
If Err Then
err.Clear
Conn.close:set Conn=nothing
Response.Write "对不起，数据库连接出错。"
Response.End
End If
End Function
%>
<%
Function access_conn()
On Error Resume Next
dim Dbpath,Conn
Dbpath=server.MapPath("mydata.mdb")
set Conn=server.createObject("ADODB.connection")
Conn.open "data source="&dbpath&";provider=microsoft.Jet.OLEDB.4.0;"
set access_conn=Conn
If Err Then
Response.Write "对不起，数据库连接出错。"
err.Clear
Conn.close:set Conn=nothing
Response.End
End If
End Function
%>
<body>
<table width="50%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#000000">
<%
set rsa=server.CreateObject("Adodb.recordset")
rsa.open "SElect distinct username,userinfo.id from userinfo right join cart on userinfo.id=cart.userid ",access_conn,1,2
do while not rsa.eof
%>
<tr>
<td width="100" bgcolor="#FFFFFF">
<%
response.write rsa(0)
%>
</td>
<td width="393" bgcolor="#FFFFFF">
<%
set rs=server.CreateObject("Adodb.recordset")
rs.open "Select *from cart where cart.userid="&rsa(1)&"",access_conn,1,2
do while not rs.eof
response.write rs("bookname")&"<br/>"
rs.movenext
loop
%>
</td>
<%
rsa.movenext
loop
%>
</tr>
</table>
<table width="50%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#000000" style="margin:20px;">
<!--
SELECT * FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.OTHERKEY=XXXX与SELECT * FROM A LEFT JOIN B ON A.ID=B.ID AND B.OTHERKEY=XXXX
是不一样的；后者相当于SELECT * FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.OTHERKEY=XXXX OR B.OTHERKEY IS NULL
-->
<%
set rsa=server.CreateObject("Adodb.recordset")
rsa.open "SElect distinct username,userinfo.id from userinfo right join cart on userinfo.id=cart.userid where userinfo.username='syan'",access_conn,1,2
do while not rsa.eof
%>
<tr>
<td width="100" bgcolor="#FFFFFF">
<%
response.write rsa(0)
%>
</td>
<td width="393" bgcolor="#FFFFFF">
<%
set rs=server.CreateObject("Adodb.recordset")
rs.open "Select *from cart where cart.userid="&rsa(1)&"",access_conn,1,2
do while not rs.eof
response.write rs("bookname")&"<br/>"
rs.movenext
loop
%>
</td>
<%
rsa.movenext
loop
%>
</tr>
</table>
<table width="50%" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#000000">
<%
set rsa=server.CreateObject("adodb.recordset")
rsa.open "select userinfo.username ,cart.bookname from cart right join userinfo on cart.userid=userinfo.id left join press on cart.pressid=press.id order by username",sql_conn,1,2 '仅限sql server 数据库,access不支持
%>
<%do while not rsa.eof%>
<tr>
<td width="100" bgcolor="#FFFFFF"><%=rsa(0)%></td><td bgcolor="#FFFFFF"><%=rsa(1)%></td>
</tr>
<%rsa.movenext
loop%>
</table>
<%
sql="select distinct userid,username from cart right join userinfo on cart.userid=userinfo.id"
set rsa=server.createobject("adodb.recordset")
rsa.open sql,sql_conn,1,2
do while not rsa.eof
response.write rsa(0) & " "&rsa(1) &"<br/>"
rsa.movenext
loop
%>
</body>
</html>
如有问题，可以QQ搜索群1028468525加入群聊，欢迎一起研究技术
转载请注明：sql联合查询 出自老鄢博客 | 欢迎分享