select中求和的问题!

xh416 2003-09-25 05:30:55

代码如下:
sql="select sum(本队公安车辆) as ce, sum(本队公安人员) as ren, sum(消防人员死亡) as death, sum(消防人员重伤) as shoushan, sum(抢救人员) as qjren, sum(抢救财产) as qjmoney from "&s_year&" where "&session("txtsql") '
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql,application("conn"),1,1
num_b_c=num_b_c+rs("ce")
num_b_r=num_b_r+rs("ren")
num_g_c=num_g_c+rs("death")
num_g_r=num_g_r+rs("shoushan")
num_z_c=num_z_c+rs("qjren")
num_y_c=num_y_c+rs.Fields.Item("qjmoney").Value'rs("qjmoney")
response.write cstr(rs.recordcount)+" "+sql+" "
rs.close

结果如下:
1
select sum(本队公安车辆) as ce, sum(本队公安人员) as ren, sum(消防人员死亡) as death, sum(消防人员重伤) as shoushan, sum(抢救人员) as qjren, sum(抢救财产) as qjmoney from f_yPJ2002 where 类别='2300' and 单位编码 like '*4201*' and (接警时间>=#2002-1-1# and 接警时间<#2003-12-31#)

说明数据链接没问题,可是num_***之类的值一个都不能显示,不知是为什么?

...全文

135 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

xxrl 2003-09-26

打赏
举报

啊啊阿啊啊阿啊啊阿啊啊阿?不可能吧?楼主说的好像相反耶!!

xh416 2003-09-26

打赏
举报

问题已经自已搞定原,来是%与*的问题!
发现在一个查询语句中如果有多个匹配符时要用*而不能用%,如果只有一个时就只能用%, 不知道为咱!

xiaobird1 2003-09-25

打赏
举报

刚刚看了看代码，

if table_y=1 then

请确定，进入这个if了？

xh416 2003-09-25

打赏
举报

自已顶一下!

xh416 2003-09-25

打赏
举报

说明一下思路,先由用户选择一个时间段来查询,判断这个时间段是否每年都有数据表,一年是个表.
如表存在就累加值,否则不加!

xh416 2003-09-25

打赏
举报

全部代码:
<%
'on error resume next
response.buffer=true
response.expires=0

dim rs
Dim sql
Dim radio,start,over,start_year,over_year,i,http_sql

radio=request.QueryString("radio") '报表类型
start=request.QueryString("start") '起始年
over=request.QueryString("over") '终止年
dwbm=request.QueryString("dwbm") '单位编码

start_year=year(start)
over_year=year(over)

num_money=0
num_death=0
num_b_c=0
num_b_r=0
num_g_c=0
num_g_r=0
num_z_r=0
num_z_c=0
num_y_r=0
num_y_c=0
num_s=0
'num_ss=0
num_qj=0

dim s_year
dim table_y

Const adSchemaTables = 20
adSchemaColumns = 4

Set rstSchema = application("conn").OpenSchema(adSchemaColumns)

for i=start_year to over_year step 1
table_y=0 '表是否存在,0不存在,1存在
s_year="f_yPJ"+cstr(i)
Do Until rstSchema.EOF
if rstSchema("Table_name")=s_year then
table_y=1
exit do
else
table_y=0
end if
rstSchema.MoveNext
Loop
if table_y=1 then
sql="select sum(本队公安车辆) as ce, sum(本队公安人员) as ren, sum(消防人员死亡) as death, sum(消防人员重伤) as shoushan, sum(抢救人员) as qjren, sum(抢救财产) as qjmoney from "&s_year&" where "&session("txtsql") '
Set rs = Server.CreateObject("ADODB.Recordset")
rs.open sql,application("conn"),1,1
num_b_c=num_b_c+rs("ce")
num_b_r=num_b_r+rs("ren")
num_g_c=num_g_c+rs("death")
num_g_r=num_g_r+rs("shoushan")
num_z_c=num_z_c+rs("qjren")
num_y_c=num_y_c+rs("qjmoney")
response.write cstr(rs.recordcount)+" "+sql+" "
rs.close
end if
next

%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>统计结果</title>
<link href="image/css.css" rel="stylesheet" type="text/css">
</head>

<body>
<table width="577" border="1" align="center" cellpadding="0" cellspacing="0" bordercolorlight="#000000" bordercolordark="#FFFFFF">
<tr>
<td width="650" align="center" bgcolor="#CCFFFF" class="title">统计结果</td>
</tr>
<tr>
<td class="text"> 　<%=formatdatetime(start,vblongdate)%>到<%=formatdatetime(over,vblongdate)%>的火灾记录共查询到 <%=request.QueryString("num_fire")%> 起其中：</td>
</tr>
<tr>
<td class="text">　　　</td>
</tr>
<tr>
<td class="text"> 　　　本消防队投入力量：车辆<%=num_b_c&"sss"%>台，人员<%=num_b_r%>人</td>
</tr>
<tr>
<td class="text"> </td>
</tr>
<tr>
<td class="text">　　　消防人员伤亡：死亡<%=num_g_c%>人，受伤<%=num_g_r%>人</td>
</tr>
<tr>
<td class="text"> </td>
</tr>
<tr>
<td class="text">　　　战斗成果：抢救<%=num_z_c%>人，抢救财产<%=num_y_c%>千元</td>
</tr>
<tr>
<td align="center" class="text"> </td>
</tr>
<tr>
<td align="center" class="text"> <input type="button" name="Submit" value="打印" class="whitebutton" onClick="javascript:self.print();">
<input type="button" name="Submit2" value="返回" class="whitebutton" onClick="javascipt:history.back(1)">
<input type="button" name="Submit3" value="刷新" class="whitebutton" onclick="javascript:location.reload()">
</td>
</tr>
</table>
</body>
</html>
<%
'set Recordset1=nothing
%>

xiaobird1 2003-09-25