# 这样的查询怎么写（主要是表数据的运算）！多谢

www.MyException.Cn  网友分享于：2013-03-10  浏览：3次

ACCESS数据库

bpname       pfname bs yuefen fen1 fen2

将票数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)
)
------解决方案--------------------

------解决方案--------------------
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）//逗号
------解决方案--------------------

------解决方案--------------------
<%
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 "