其主要原因是我在设计一个信息排名的asp动态网页的时候，rs14需要对rs11、rs11两个记录集进行引用，我不知道如何引用，如果能rs14里面能够引用前面的rs11和rs12也就不用这么麻烦了，如：“SELECT Rs12.Author”这样写不行的，前面的rs11和rs12测试都是正常。
<%
set rs11=server.createobject("adodb.recordset")
sql11="select Author,Count(NewsID) AS NewsID之计数 from "& db_EC_News_Table &" WHERE Author<>'admin' GROUP BY Author"
rs11.open sql11,conn,1,1
%>
<%
set rs12=server.createobject("adodb.recordset")
sql12="TRANSFORM Count(WY_News.NewsID) AS NewsID之总计"
sql12=sql12&" SELECT WY_News.Author"
sql12=sql12&" FROM "& db_EC_News_Table &""
sql12=sql12&" WHERE WY_News.Author<>'admin'"
sql12=sql12&" GROUP BY WY_News.Author"
sql12=sql12&" PIVOT WY_News.E_typeid"
rs12.open sql12,conn,1,1
%>
<%
set rs14=server.createobject("adodb.recordset")
sql14="INSERT INTO "& db_N_Order_Table &" Author, submitted, bulletin, Public, Administrative, County, Video, Integral, used "
sql14=sql14&" SELECT Rs12.Author, nz(rs11.NewsID之计数,0) AS bs, nz(rs12.17,0) AS 17, nz(rs12.16,0) AS 16, nz(rs12.5,0) AS 5, nz(rs12.4,0) AS 4, nz(rs12.8,0) AS 8, nz(rs12.17,0)*1.2+nz(rs12.16,0)*1+nz(rs12.5,0)*1+nz(rs12.4,0)*1+nz(rs12.8,0)*1.5 AS Int, nz(rs12.17,0)+nz(rs12.16,0)+nz(rs12.5,0)+nz(rs12.4,0)+nz(rs12.8,0) AS used"
sql14=sql14&" FROM rs11 INNER JOIN rs12 ON rs11.Author = rs12.Author"
sql14=sql14&" ORDER BY nz(rs12.17,0)*1.2+nz(rs12.16,0)*1+nz(rs12.5,0)*1+nz(rs12.4,0)*1+nz(rs12.8,0)*1.5 DESC"
rs14.open sql14,conn,1,1
%>
对付一个更新查询需要这样劳民伤财吗？呵呵。
在我写的联合查询和交叉查询基础上，语句可以简单的如此：
dim ssql as string
ssql="INSERT INTO N_order ( Author, submitted, bulletin, [Public], Administrative, County, Video, Integral, used ) "
ssql=ssql & "SELECT Author,bs,[17],[16],[5],[4],[8],"
ssql=ssql & "nz([17],0)*1.2+nz([16],0)*1+nz([5],0)*1+nz([4],0)*1+nz([8],0)*1.5 AS [Int],"
ssql=ssql & "nz([17],0)+nz([16],0)+nz([5],0)+nz([4],0)+nz([8],0) AS used "
ssql=ssql "FROM 交叉查询"
CurrentDb.Execute ssql
但是我不晓得在asp的代码中如何引用“交叉查询”
我的意思是现在删除 交叉查询 和 联合查询 后，仍然可以实现追加功能
即使要用ADO处理，也可以简单的写为：
Dim rs0 As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim i As Long
rs0.Open "N_order", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs1.Open "交叉查询", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
for i=1 to rs1.RecordCount
rs0.AddNew
rs0!Author=rs1!Author
rs0!submitted=rs1!bs
rs0!bulletin=rs1![17]
rs0![Public]=rs1![16]
rs0!Administrative=rs1![5]
rs0!County=rs1![4]
rs0!Video=rs1![8]
rs0!Integral=nz(rs1![17],0)*1.2+nz(rs1![16],0)*1+nz(rs1![5],0)*1+nz(rs1![4],0)*1+nz(rs1![8],0)*1.5
rs0!used=nz(rs1![17],0)+nz(rs1![16],0)+nz(rs1![5],0)+nz(rs1![4],0)+nz(rs1![8],0)
rs0.Update
rs1.movenext
next
rs0.Close
rs1.Close
Set rs0 = Nothing
Set rs1 = Nothing
“我的意思是现在删除 交叉查询 和 联合查询 后，仍然可以实现追加功能。”
这个想法叫做简单问题复杂，阳关道不走，非要走独木桥。对此表示强烈的鄙视！鄙视完了还要告诉你办法：
Function 联合查询() as string
dim ssql as string
ssql="SELECT WY_News.Author, WY_News.E_typeid, Count(WY_News.NewsID) AS 计数 "
ssql=ssql & "FROM WY_News "
ssql=ssql & "WHERE (((WY_News.Author)<>"admin")) "
ssql=ssql & "GROUP BY WY_News.Author, WY_News.E_typeid "
ssql=ssql & "UNION ALL SELECT WY_News.Author, "bs" AS E_typeid, Count(WY_News.NewsID) AS 计数 "
ssql=ssql & "FROM WY_News "
ssql=ssql & "WHERE (((WY_News.Author)<>"admin")) "
ssql=ssql & "GROUP BY WY_News.Author"
联合查询=ssql
end Function
Function 交叉查询(Strsql as string) as String
dim ssql as string
ssql="TRANSFORM Sum(a.计数) AS 计数之总计 "
ssql=ssql & "SELECT a.Author "
ssql=ssql & "FROM " & Strsql & " AS a "
ssql=ssql & "GROUP BY a.Author "
ssql=ssql & "PIVOT a.E_typeid"
交叉查询=ssql
end Function
Sub 追加查询()
Dim rs0 As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Dim ssql as string
Dim i As Long
ssql=交叉查询(联合查询)
rs0.Open "N_order", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs1.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
for i=1 to rs1.RecordCount
rs0.AddNew
rs0!Author=rs1!Author
rs0!submitted=rs1!bs
rs0!bulletin=rs1![17]
rs0![Public]=rs1![16]
rs0!Administrative=rs1![5]
rs0!County=rs1![4]
rs0!Video=rs1![8]
rs0!Integral=nz(rs1![17],0)*1.2+nz(rs1![16],0)*1+nz(rs1![5],0)*1+nz(rs1![4],0)*1+nz(rs1![8],0)*1.5
rs0!used=nz(rs1![17],0)+nz(rs1![16],0)+nz(rs1![5],0)+nz(rs1![4],0)+nz(rs1![8],0)
rs0.Update
rs1.movenext
next
rs0.Close
rs1.Close
Set rs0 = Nothing
Set rs1 = Nothing
end Sub
点击下载此附件
点击下载此附件
