会员信息列表及统计功能的实现（sql多表left join）
相关文件:
1.user_info.asp(提示信息)

相关数据库表:
Article_Member
Article_friend
Article_News
Article_Comment
Article_GuestBook

核心功能:

（一条SQL语句实现多表left join，并实现相关统计）
select *,bbb.counthy,ccc.countrj,ddd.countpl,eee.countly from (((Article_Member left join (select fid,count(*) as counthy from Article_friend group by fid) bbb on Article_Member.M_login=bbb.fid) left join (select N_author,count(*) as countrj from Article_News group by N_author) ccc on Article_Member.M_login=ccc.N_author) left join (SELECT C_name,count(*) as countpl FROM Article_Comment group by C_name) ddd on Article_Member.M_login=ddd.C_name) left join (SELECT G_username,count(*) as countly FROM Article_GuestBook group by G_username) eee on Article_Member.M_login=eee.G_username where M_id>0

核心代码:

列表页面:user_info.asp
.......................................................................
<!-- 程序部分 -->
<%
PageShowSize = 5            '每页显示多少个页
MyPageSize   = 10          '每页显示多少条
If Not IsNumeric(Request("page")) Or IsEmpty(Request("page")) Or Request("page") <=0 Then
MyPage=1
Else
MyPage=Int(Abs(Request("page")))
End if
Set rs=Server.CreateObject("ADODB.RecordSet")
sql="select *,bbb.counthy,ccc.countrj,ddd.countpl,eee.countly from (((Article_Member left join (select fid,count(*) as counthy from Article_friend group by fid) bbb on Article_Member.M_login=bbb.fid) left join (select N_author,count(*) as countrj from Article_News group by N_author) ccc on Article_Member.M_login=ccc.N_author) left join (SELECT C_name,count(*) as countpl FROM Article_Comment group by C_name) ddd on Article_Member.M_login=ddd.C_name) left join (SELECT G_username,count(*) as countly FROM Article_GuestBook group by G_username) eee on Article_Member.M_login=eee.G_username where M_id>0"
if request("memName")<>"" then
sql=sql&" and M_name = '"&request("memName")&"'"
end if
if request("N_commond")<>"" then
sql=sql&" and N_commond = "&request("N_commond")&""
end if
if request("regDate")<>"" then
sql=sql&" and M_regdate = #"&request("regDate")&"#"
end if
if request("M_province")<>"" then
sql=sql&" and M_province = '"&request("M_province")&"'"
end if
if request("M_city")<>"" then
sql=sql&" and M_city = '"&request("M_city")&"'"
end if
if request("ob")<>"" then
ob = request("ob")
if ob=1 then
sql=sql+" order by M_integral desc"
end if
if ob=3 then
sql=sql+" order by countrj desc"
end if
if ob=4 then
sql=sql+" order by countpl desc"
end if
if ob=5 then
sql=sql+" order by M_hits desc"
end if
if ob=6 then
sql=sql+" order by M_lastdate desc"
end if
else
sql=sql+" order by M_id desc"
end if
sql=Replace(sql, "where and", "where")
rs.Open sql,conn,1,1
if not rs.EOF then
rs.PageSize     = MyPageSize
MaxPages         = rs.PageCount
rs.absolutepage = MyPage
total            = rs.RecordCount
%>
<%
for i=1 to rs.PageSize
if not rs.EOF then
%>
列表信息     
<%
rs.MoveNext
end if
next
end if
%>
<!-- 程序部分 -->
....................................................................