ACCESS数据库
表如下：
bpname pfname bs yuefen fen1 fen2
张三 李四 3 200704 8 10
张三 李四 1 200704 10 7
张三 李四 1 200704 10 10
张三 李四 2 200704 10 10
被评人 评分人 票数 时间 第一项得分 第二项得分
现在是要将票数bs为3的 得分乘以3
将票数bs为2的 得分乘以2
将票数bs为1的 得分乘以1
然后算出总分 除以总票数...算出平均得分..
请各位帮忙，我在线等
------解决方案--------------------
试试，为了直观，分成多行写成这样：
select round(
((select sum(fen1+fen2)*3 from pc_result where bs=3)
+(select sum(fen1+fen2)*3 from pc_result where bs=2)
+(select sum(fen1+fen2)*1 from pc_result where bs=1))
/(select sum(bs)*2 from pc_result)
)
------解决方案--------------------
最后少了 from pc_result
------解决方案--------------------
select round(...) as average from pc_result，是这个意思吗？
------解决方案--------------------
select bpname, (sum(bs * fen1) / sum(bs)) as 平均分1,sum(bs * fen2)/sum(bs) as 平均分2 from 表 where yuefen= '200704 'group by bpname
------解决方案--------------------
sum(fen1+...fen5)
round(语句,2）//逗号
------解决方案--------------------
设置变量，分几次查询，判断结果集是否为空，为空则置为0,把结果先保存到变量中然后再输出。
------解决方案--------------------
<%
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 "