SQL数据库中有两张表,表A 和表B ,表A 有两列数据A1 和A2,
表B有两列数据B1 和B2,如下所示:
表A 表B
A1 A2 B1 B2
5 6 15 20
4 4 20 30
查询输出到表C,表C为ASP网页表格,表C有8列,C1、C2对应表A的A1、A2,
C3、C4对应表B的B1、B2,C5为C1/C3的乘积,C6为C1/C4的乘积,C7为C2/C3的乘积,
C8为C2/C4的乘积,如下:
表C
C1 C2 C3 C4 C5 C6 C7 C8
5 6 15 20 75 100 90 120
4 4 20 30 80 120 80 120
现在想将C5、C6、C7、C8这四列单独求和,如下:
表C
C1 C2 C3 C4 C5 C6 C7 C8
5 6 15 20 75 100 90 120
4 4 20 30 80 120 80 120
155 220 170 240
请问应该怎样做?恳请赐教。。
表B有两列数据B1 和B2,如下所示:
表A 表B
A1 A2 B1 B2
5 6 15 20
4 4 20 30
查询输出到表C,表C为ASP网页表格,表C有8列,C1、C2对应表A的A1、A2,
C3、C4对应表B的B1、B2,C5为C1/C3的乘积,C6为C1/C4的乘积,C7为C2/C3的乘积,
C8为C2/C4的乘积,如下:
表C
C1 C2 C3 C4 C5 C6 C7 C8
5 6 15 20 75 100 90 120
4 4 20 30 80 120 80 120
现在想将C5、C6、C7、C8这四列单独求和,如下:
表C
C1 C2 C3 C4 C5 C6 C7 C8
5 6 15 20 75 100 90 120
4 4 20 30 80 120 80 120
155 220 170 240
请问应该怎样做?恳请赐教。。
from 表
A表跟B表通过SQL进行连接
是得不到c表这样的结果的
2 还有是C表是不是已经有结果了,
如果有就很简单
直接
SELECT SUM(C4),SUM(C5),SUM(C6),SUM(C7) FROM C
3 如果想通过A B 量表来统计,请先把我讲的第一条看下
还是把问题讲明白些
你可以设置四个变量分别保存C5/C6/C7/C8
求和的值,在计算C5/C6/C7/C8这几列的结果分别也顺便
保存下求和的值
假如C5列求和值变量为lngSumC5,你在
<TD>中插入 <%rsCxx*rsCxx%>可以这样写
<%response.write(rsCxx*rsCxx)
lngSumC5=lngSumC5+rsCxx*rsCxx%
%>
A0 A1 A2 B0 B1 B2
1 5 6 1 15 20
2 4 4 2 20 30
这样设
INSERT INTO C(
C1
, C2
, C3
, C4
)
SELECT A1
, A2
, B1
, B2
FROM A
, B
WHERE A0=B0UPDATE C
SET C5 = C1 * C3
, C6 = C1 * C4
, C7 = C2 * C3
, C8 = C2 * C4SELECT SUM(C5)
, SUM(C6)
, SUM(C7)
, SUM(C8)
FROM C
<%
sub showtable
do while not rs.eof
%>
<tr bgcolor='#ffffff'>
<td nowrap align=center><%=rs("prod_no")%></td>
<td nowrap align=center><%=rs("prod_type")%></td>
<td nowrap align=center><%=rs("prod_pinming")%></td>
<td nowrap align=center><%if isnull(rs("in_sjrk")) then %>-<%else%><%=rs("in_sjrk")%><%end if%></td>
<td nowrap align=center><%if isnull(rs("in_yjrk")) then %>-<%else%><%=rs("in_yjrk")%><%end if%></td>
<td nowrap align=center><%=rs("in_sjkc")%></td>
<td nowrap align=center><%=rs("in_yjkc")%></td>
<td nowrap align=center><%=rs("prod_cbj")%></td>
<td nowrap align=center><%=rs("prod_scj")%></td>
<td nowrap align=center><%=rs("in_sjkc")*rs("prod_cbj")%></td>(注**C5**)
<td nowrap align=center><%=rs("in_sjkc")*rs("prod_scj")%></td>(注**C6**)
<td nowrap align=center><%=rs("in_yjkc")*rs("prod_cbj")%></td>(注**C7**)
<td nowrap align=center><%=rs("in_yjkc")*rs("prod_scj")%></td>(注**C8**)
</tr><%
'rs3.close
rs.movenext
loop
response.write "<tr bgcolor='#ECECF0'>"
response.write "<td nowrap colspan=9 align=right>统计:</td>"
Response.Write "<td nowrap align=center>"&SUM&"</td>"(注**C5**)
Response.Write "<td nowrap align=center>"&SUM&"</td>"(注**C6**)
Response.Write "<td nowrap align=center>"&SUM&"</td>"(注**C7**)
Response.Write "<td nowrap align=center>"&SUM&"</td>"(注**C8**)
response.write "</tr>"
end sub
%>
<%
sub showtable
DIM dblSumC5
DIM dblSumC6
DIM dblSumC7
DIM lngSumC8
dblSumC5 =0
dblSumC6 =0
dblSumC7=0
dblSumC8 =0
do while not rs.eof
%>
<tr bgcolor='#ffffff'>
<td nowrap align=center> <%=rs("prod_no")%> </td>
<td nowrap align=center> <%=rs("prod_type")%> </td>
<td nowrap align=center> <%=rs("prod_pinming")%> </td>
<td nowrap align=center> <%if isnull(rs("in_sjrk")) then %>- <%else%> <%=rs("in_sjrk")%> <%end if%> </td>
<td nowrap align=center> <%if isnull(rs("in_yjrk")) then %>- <%else%> <%=rs("in_yjrk")%> <%end if%> </td>
<td nowrap align=center> <%=rs("in_sjkc")%> </td>
<td nowrap align=center> <%=rs("in_yjkc")%> </td>
<td nowrap align=center> <%=rs("prod_cbj")%> </td>
<td nowrap align=center> <%=rs("prod_scj")%> </td>
<td nowrap align=center> <%=rs("in_sjkc")*rs("prod_cbj")%> </td>(注**C5**)
<td nowrap align=center> <%=rs("in_sjkc")*rs("prod_scj")%> </td>(注**C6**)
<td nowrap align=center> <%=rs("in_yjkc")*rs("prod_cbj")%> </td>(注**C7**)
<td nowrap align=center> <%=rs("in_yjkc")*rs("prod_scj")%> </td>(注**C8**)
</tr> <%
dblSumC5=dblSumC5+rs("in_sjkc")*rs("prod_cbj") '你要注意数据库中该字段是否为空,判断我就不加了
dblSumC6=dblSumC5+rs("in_sjkc")*rs("prod_scj") '剩下两个类似,自己加吧
'rs3.close
rs.movenext
loop
response.write " <tr bgcolor='#ECECF0'>"
response.write " <td nowrap colspan=9 align=right>统计: </td>"
Response.Write " <td nowrap align=center>"&dblSumC5&" </td>"(注**C5**)
Response.Write " <td nowrap align=center>"&dblSumC6&" </td>"(注**C6**)
Response.Write " <td nowrap align=center>"&dblSumC7&" </td>"(注**C7**)
Response.Write " <td nowrap align=center>"&dblSumC8&" </td>"(注**C8**)
response.write " </tr>"
end sub
%>
<%
sub showtable
DIM dblSumC5
DIM dblSumC6
DIM dblSumC7
DIM lngSumC8
dblSumC5 =0
dblSumC6 =0
dblSumC7=0
dblSumC8 =0
do while not rs.eof
%>
<tr bgcolor='#ffffff'>
<td nowrap align=center> <%=rs("prod_no")%> </td>
<td nowrap align=center> <%=rs("prod_type")%> </td>
<td nowrap align=center> <%=rs("prod_pinming")%> </td>
<td nowrap align=center> <%if isnull(rs("in_sjrk")) then %>- <%else%> <%=rs("in_sjrk")%> <%end if%> </td>
<td nowrap align=center> <%if isnull(rs("in_yjrk")) then %>- <%else%> <%=rs("in_yjrk")%> <%end if%> </td>
<td nowrap align=center> <%=rs("in_sjkc")%> </td>
<td nowrap align=center> <%=rs("in_yjkc")%> </td>
<td nowrap align=center> <%=rs("prod_cbj")%> </td>
<td nowrap align=center> <%=rs("prod_scj")%> </td>
<td nowrap align=center> <%=rs("in_sjkc")*rs("prod_cbj")%> </td>(注**C5**)
<td nowrap align=center> <%=rs("in_sjkc")*rs("prod_scj")%> </td>(注**C6**)
<td nowrap align=center> <%=rs("in_yjkc")*rs("prod_cbj")%> </td>(注**C7**)
<td nowrap align=center> <%=rs("in_yjkc")*rs("prod_scj")%> </td>(注**C8**)
</tr> <%
dblSumC5=dblSumC5+rs("in_sjkc")*rs("prod_cbj") '你要注意数据库中该字段是否为空,判断我就不加了
dblSumC6=dblSumC5+rs("in_sjkc")*rs("prod_scj") '剩下两个类似,自己加吧
'rs3.close
rs.movenext
loop
response.write " <tr bgcolor='#ECECF0'>"
response.write " <td nowrap colspan=9 align=right>统计: </td>"
Response.Write " <td nowrap align=center>"&dblSumC5&" </td>"(注**C5**)
Response.Write " <td nowrap align=center>"&dblSumC6&" </td>"(注**C6**)
Response.Write " <td nowrap align=center>"&dblSumC7&" </td>"(注**C7**)
Response.Write " <td nowrap align=center>"&dblSumC8&" </td>"(注**C8**)
response.write " </tr>"
end sub
%>
就知道了