关于分组求和的问题
ACCESS数据库表：
bpname pfname bs yuefen fen1 fen2
张三 李四 3 200704 8 10
张三 李四 1 200704 10 7
张三 李四 1 200704 10 10
张三 李四 2 200704 10 10
赵五 李四 3 200704 8 10
赵五 李四 1 200704 10 7
赵五 李四 1 200704 10 10
赵五 李四 2 200704 10 10
我查询的代码是这样写的：
<%
set rs=server.createobject( "adodb.recordset ")
set rs2=server.createobject( "adodb.recordset ")
str1= "select * from pc_one_all_result where bs=3 "
rs.open str1,conn,1,1
if not rs.eof then
sqlstr= "select bpname,round(((select sum(fen1+fen2+fen3+fen4+fen5)*3 from pc_one_all_result where bs=3 ) "
else
sqlstr= "selct bpname,round(((0 "
end if
rs.close
str2= "select * from pc_one_all_result where bs=2 "
rs.open str2,conn,1,1
if not rs.eof then
sqlstr=sqlstr & "+(select sum(fen1+fen2+fen3+fen4+fen5)*2 from pc_one_all_result where bs=2) "
else sqlstr=sqlstr & "+0 "
end if
rs.close
str3= "select * from pc_one_all_result where bs=1 "
rs.open str3,conn,1,1
if not rs.eof then
sqlstr=sqlstr & "+(select sum(fen1+fen2+fen3+fen4+fen5)*1 from pc_one_all_result where bs=1)) "
else sqlstr=sqlstr & "+0) "
end if
rs.close
sqlstr=sqlstr & "/(select sum(bs) from pc_one_all_result ),2) as fenz from pc_one_all_result GROUP BY bpname ORDER by bpname desc "
rs2.open sqlstr,conn,1,1
if rs2.eof then%>
<%else%>
<% do while not rs2.eof%>
<td> <%=rs2( "bpname ")%> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>
</td>
<td> </td>
<td bgcolor= "#FFFF99 "> <%=rs2( "fenz ")%> </td>
</tr>
<% rs2.movenext
loop
rs2.close
set rs2=nothing
end if
%>
本想要的结果是分别算出张三的平均分和赵五的平均分，但是查询出的结果把所有的记录都算进去了，把赵五的记录算了张三的，张三的也算进了赵五的....
烦请各位帮忙指点！
[解决办法]
select sum(fen1+fen2+fen3) from table where bpname= "张三 "
[解决办法]
select distinct bpname,round(...) ...
[解决办法]
试试
select distinck bpname,round(
((select sum(fen1+fen2+fen3+fen4+fen5)*3 from pc_one_all_result b where b.bs=3 and a.bpname=b.bpname)
+(select sum(fen1+fen2+fen3+fen4+fen5)*2 from pc_one_all_result c where c.bs=2 and a.bpname=c.bpname)
+(select sum(fen1+fen2+fen3+fen4+fen5)*1 from pc_one_all_result d where d.bs=1 and a.bpname=d.bpname))
/(select sum(bs) from pc_one_all_result ),2) as fenz
from pc_one_all_result a
GROUP BY bpname ORDER by bpname desc
[解决办法]
sorry,是distinct，不是distinck。
