asp + sql2000 多表联合查询记录总数出错

[nethp]

出现了奇怪的问题 如题

在查询分析器里面数据正确

select ClassName,(select count(*) from Sys_XzAUser as a left join Sys_XzAUser as b on b.id=a.XzAUserID where a.Types=0 and b.Types=0 and a.XzType=2 and (b.DepartmenNo=Sys_Departmen.DepartmenNo or b.DepartmenNoto=Sys_Departmen.DepartmenNo)) as tt
from Sys_Departmen
where left(DepartmenNo,2) = '05' and DepartmenId<>0 and (left(classname,2)='业务' or left(classname,2)='服务') and right(classname,1)='部'

业务一部 3
业务二部 0
业务三部 0
业务四部 0
业务五部 0
业务六部 0

影响行数 6行

可是在asp里面 出现的数据想都想不通
<%
Set Rs=Server.Createobject("Adodb.Recordset")
SQL="select ClassName,(select count(*) from Sys_XzAUser as a left join Sys_XzAUser as b on b.id=a.XzAUserID where a.Types=0 and b.Types=0 and a.XzType="&types&" and (b.DepartmenNo=Sys_Departmen.DepartmenNo or b.DepartmenNoto=Sys_Departmen.DepartmenNo)) as tt from Sys_Departmen where left(DepartmenNo,"&m&") = '"&bm&"' and DepartmenId<>0 and (left(classname,"&m&")='"&ValidFlogs&"' or left(classname,2)='"&ValidFlogto&"') and right(classname,1)='部'"
Rs.Open Sql,Conn,1,3
if not (rs.bof and rs.eof) then
Dim total
ReDim total(rs.RecordCount,2)
for i=1 to rs.RecordCount
%>
<p><span><%=rs(0)%></span><span><%=rs("tt")%></span></p>
<%
rs.movenext
if rs.eof then exit for
next
end if
rs.close
set rs=nothing
end if
%>
显示结果：

统计结果
业务一部 63
业务二部 78
业务三部 66
业务四部 63
业务五部 120
业务六部 48


什么原因？？？

[yyh112]

建立临时表试试
conn.execute "Select 要用的字段 Into #临时表名 From Sys_XzAUser as a left join Sys_XzAUser as b on b.id=a.XzAUserID where a.Types=0 and b.Types=0 and a.XzType=2"

在用 select count(*) from 临时表 where 条件