如何在同一ADODB连接中执行两个SQL查询实现切割与未切割结果并列展示

问题原因 #

你当前代码的核心问题是SQL变量赋值逻辑错误：第一次给strSQL赋值的已切割查询语句，被第二次的未切割查询直接覆盖了，最终rsJobs仅加载了第二个查询，返回字段只有NotCut_70mm，读取不存在的wip_70mm字段时自然会报错。

你不需要拆分多个连接/记录集操作，将多个统计逻辑合并为同一个查询返回即可，后续新增同类统计也可以直接扩展，以下是可直接运行的修改方案：

set connJobIndex=Server.CreateObject("ADODB.Connection")
set rsJobs=Server.CreateObject("ADODB.Recordset")
connJobIndex.open Application("connJobIndex_Orders")

' 合并多组统计为同一个查询，后续新增统计直接加新的子查询即可
strSQL = "SELECT "
' 第一个统计项：已切割70mm
strSQL = strSQL & " (SELECT TOP 1 sum(Production.Quantity) over (partition by Production.Quantity order by Production.Quantity) "
strSQL = strSQL & " FROM Heading INNER JOIN Production ON Heading.JobKeyID = Production.JobKeyID INNER JOIN Tracking ON Production.JobKeyID = Tracking.JobKeyID AND Production.ItemKeyID = Tracking.ItemKeyID "
strSQL = strSQL & " WHERE (dbo.Production.ProductID IN (13, 18, 42, 43, 14, 152, 162, 155, 157, 156, 153, 163, 168, 167, 164)) and DateDelivery between '" & datestring & "' and '" & datestringEnd & "'"
strSQL = strSQL & " GROUP BY Production.JobKeyID, Production.ItemKeyID, Production.Quantity "
strSQL = strSQL & " HAVING (MAX(Tracking.StageID) >= 110 and max(stageid) < 200 )) as wip_70mm, "
' 第二个统计项：未切割70mm
strSQL = strSQL & " (SELECT count(*) "
strSQL = strSQL & " FROM Heading INNER JOIN Production ON Heading.JobKeyID = Production.JobKeyID LEFT JOIN Tracking ON Production.JobKeyID = Tracking.JobKeyID AND Production.ItemKeyID = Tracking.ItemKeyID "
strSQL = strSQL & " WHERE (dbo.Production.ProductID IN (13, 18, 42, 43, 14, 152, 162, 155, 157, 156, 153, 163, 168, 167, 164)) and  DateDelivery between '" & datestring & "' and '" & datestringEnd & "'"
strSQL = strSQL & " GROUP BY tracking.JobKeyID "
strSQL = strSQL & " HAVING (max(stageid) <110 or max(stageid) IS NULL)) as NotCut_70mm "

rsjobs.Open strSQL,connJobIndex

' 输出表格，已修正原代码多余的闭合标签、遗漏的记录移动逻辑
response.write "<table>"
response.write "<tr>"
response.write "<th class=BordersAll>70mm</th>"
response.write "<th class=BordersAll>70mm NotCut</th>"
response.write "</tr>"
do until rsJobs.EOF
    response.write "<tr>"
    response.write "<td class=BordersAll>" & rsjobs("wip_70mm") & "</td>"
    response.write "<td class=BordersAll>" & rsjobs("NotCut_70mm") & "</td>"
    response.write "</tr>"
    rsJobs.MoveNext ' 必须加，否则会进入死循环
loop
response.write "</table>"

' 用完释放资源，避免连接泄漏
rsJobs.Close
set rsJobs = Nothing
connJobIndex.Close
set connJobIndex = Nothing

注意事项 #

1. 当前直接拼接日期参数的写法存在SQL注入风险，条件允许建议改用ADODB参数化查询，安全性更高
2. 后续新增同类统计，只需要在SELECT语句后按照相同格式加新的子查询、定义别名即可，不需要大幅修改后续输出逻辑

内容的提问来源于stack exchange，提问作者Rekiel97