请问各位这个SQL语句该如何优化呢？

[kevinlau2]

都是一个表里的数据，而且都是重复的语句，这些语句的作用就是从表里取出的数据，按照不同的值，赋值到不同的变量上。但是我写的这些SQL语句，造成页面读的时候特别慢。所以想优化一下这些SQL语句。请各位高手指点。

<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 1 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_ats = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 1 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_bts = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 2 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_atsxf = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 2 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_btsxf = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 3 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_atszj = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 3 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_btszj = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 4 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_aqk = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 4 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_bqk = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 5 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_aqkxf = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 5 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_bqkxf = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 6 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_aqkzj = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 6 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.open sql,conn,0,1 
	jishu_bqkzj = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 7 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_ahh = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 7 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_bhh = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 8 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_aty = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 8 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_bty = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 9 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_acr = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 9 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_bcr = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 10 and status = 0 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_agc = RS.RecordCount 'totalrec：总记录条数 
%>
<%
	set rs = server.createobject("adodb.recordset")
	sql = "select * from pz where vision = 10 and status = 1 order by id desc"
	rs.cursorlocation = 3 '使用客户端游标，可以使效率提高
	rs.Open sql,conn,1,3
	jishu_bgc = RS.RecordCount 'totalrec：总记录条数 
%>

[lzp4881]

<% jishu_ats = conn.execute("select count(*) from pz where vision = 1 and status = 0 order by id desc")(0) %> <% jishu_bts = conn.execute("select count(*) from pz where vision = 1 and status = 1 order by id desc")(0) %> ..............

[lzp4881]

忘了 order by id desc 也可以不要

[Anlige]

用group by

[Anlige]

SELECT vision, status, count(*)
FROM pz
GROUP BY vision, status;

access测试通过，只要一次查询，后面的在asp里面作if判断就好了

[kevinlau2]

引用 4 楼 Anlige 的回复:

SELECT vision, status, count(*)
FROM pz
GROUP BY vision, status;

access测试通过，只要一次查询，后面的在asp里面作if判断就好了

判断语句怎么写呢？ 请是新手，请您帮忙指点一下。谢谢您啦。

[Anlige]

通过这个sql语句，查询出一个rs， do while循环这个rs，通过判断vision和status的值，把count的值复制给已经定义的变量 set rs = server.createobject("adodb.recordset") sql = "SELECT vision, status, count(*) as total FROM pz GROUP BY vision, status" rs.Open sql,conn,1,1 do while not rs.eof if rs("vision")=10 and rs("status")=1 then jishu_bgc = rs("total") if rs("vision")=10 and rs("status")=0 then jishu_agc = rs("total") rs.movenext loop 以此类推，前提： 你把需要的变量提前赋值为0，按你的代码应该是有20个变量（因为，这个sql语句不会统计不存在的vision和status组合） 适当的时候，可以跳出循环，也能提高循环的效率，就是代码多了一些

[skillwayos]

学习下。