none
怎样对带有Sum函数的SQL语句进行除法运算? RRS feed

  • 问题

  • 我现在要更新EvaluateStatistics 表里的tutortotalscore字段,这个字段的值是两个sum求和后进行除法运算,执行的时候老是报错select语法附近有错误
    急求高手解决

    update EvaluateStatistics set tutortotalscore=
      select sum(temp)/sum(tutorhours) from
    (
    select distinct EvaluateStatistics.TeacherUserName as TeacherUserName,EvaluateStatistics.TeachTaskNo as TeachTaskNo,EvaluateStatistics.TutorHours
    as TutorHours,avg(EvaluateTeach.FinalScore)*EvaluateStatistics.TutorHours as temp  From EvaluateStatistics,EvaluateTeach
    where EvaluateStatistics.TeacherUserName=EvaluateTeach.TeacherUserName and EvaluateTeach.TeachTaskNo=EvaluateStatistics.TeachTaskNo
    group by EvaluateStatistics.TeacherUserName,EvaluateStatistics.TeachTaskNo,TutorHours
    ) as B
    where EvaluateStatistics.teacherusername=B.Teacherusername

    2010年1月19日 6:49

答案

  • update EvaluateStatistics set tutortotalscore=b.tutortotalscore
    from
     ( select Teacherusername,sum(temp)/sum(tutorhours)  as tutortotalscore
    from
    (
    select distinct EvaluateStatistics.TeacherUserName as TeacherUserName,EvaluateStatistics.TeachTaskNo as TeachTaskNo,EvaluateStatistics.TutorHours
    as TutorHours,avg(EvaluateTeach.FinalScore)*EvaluateStatistics.TutorHours as temp 
    From EvaluateStatistics,EvaluateTeach
    where EvaluateStatistics.TeacherUserName=EvaluateTeach.TeacherUserName and EvaluateTeach.TeachTaskNo=EvaluateStatistics.TeachTaskNo
    group by EvaluateStatistics.TeacherUserName,EvaluateStatistics.TeachTaskNo,TutorHours
    ) as C
    group by Teacherusername
    )B
    
    where EvaluateStatistics.teacherusername=B.Teacherusername
    
    注意括號
    ROY WU(吳熹)
    • 已标记为答案 LTL007 2010年1月20日 6:20
    2010年1月19日 8:14
    版主

全部回复

  • 去掉select


    update EvaluateStatistics set tutortotalscore=
      sum(temp)/sum(tutorhours) from
    (
    select distinct EvaluateStatistics.TeacherUserName as TeacherUserName,EvaluateStatistics.TeachTaskNo as TeachTaskNo,EvaluateStatistics.TutorHours
    as TutorHours,avg(EvaluateTeach.FinalScore)*EvaluateStatistics.TutorHours as temp  From EvaluateStatistics,EvaluateTeach
    where EvaluateStatistics.TeacherUserName=EvaluateTeach.TeacherUserName and EvaluateTeach.TeachTaskNo=EvaluateStatistics.TeachTaskNo
    group by EvaluateStatistics.TeacherUserName,EvaluateStatistics.TeachTaskNo,TutorHours
    ) as B
    where EvaluateStatistics.teacherusername=B.Teacherusername

    想不想时已是想,不如不想都不想。
    2010年1月19日 6:55
    版主
  • 去掉select不对,忘了说了我用的是2000的数据库
    我是把select出的字段赋值给tutortotalscore
    2010年1月19日 7:01
  • update EvaluateStatistics set tutortotalscore=b.tutortotalscore
    from
     ( select Teacherusername,sum(temp)/sum(tutorhours)  as tutortotalscore
    from
    (
    select distinct EvaluateStatistics.TeacherUserName as TeacherUserName,EvaluateStatistics.TeachTaskNo as TeachTaskNo,EvaluateStatistics.TutorHours
    as TutorHours,avg(EvaluateTeach.FinalScore)*EvaluateStatistics.TutorHours as temp 
    From EvaluateStatistics,EvaluateTeach
    where EvaluateStatistics.TeacherUserName=EvaluateTeach.TeacherUserName and EvaluateTeach.TeachTaskNo=EvaluateStatistics.TeachTaskNo
    group by EvaluateStatistics.TeacherUserName,EvaluateStatistics.TeachTaskNo,TutorHours
    ) as C
    group by Teacherusername
    )B
    
    where EvaluateStatistics.teacherusername=B.Teacherusername
    
    注意括號
    ROY WU(吳熹)
    • 已标记为答案 LTL007 2010年1月20日 6:20
    2010年1月19日 8:14
    版主
  • 谢谢楼上

    2010年1月20日 6:20