sum之后结果是所需要的二倍?
本人想在mysql里inner join两个表, 两个表在join之前分别自动sum出某个字段的和:
/* ALGORITHM=UNDEFINED */ select `view_essaybyuserid`.`user_id` AS `user_id`,`view_essaybyuserid`.`username` AS `username`,sum(`view_essaybyuserid`.`score`) AS `essay_score`,sum(`view_quizbyuserid`.`score`) AS `quiz_score` from (`wcv102`.`view_essaybyuserid` join `wcv102`.`view_quizbyuserid`) where (`view_essaybyuserid`.`user_id` = `view_quizbyuserid`.`user_id`) group by `view_essaybyuserid`.`user_id`
问题是出来的sum结果居然都是所需要的二倍.
请高手指教如何解决. 谢谢!
这应该是两表交叉导致的，最简单解决方法就是直接除以2，sum(aaa)/2。
这是一种治标不治本的方法，仅供参考。(*^__^*)
select a.user_id,a.username,sum(a.score) AS essay_score,sum(b.score) AS quiz_score
from `wcv102`.`view_essaybyuserid` as a inner join `wcv102`.`view_quizbyuserid` as b
on a.user_id = b.user_id group by a.user_id
这样不行?
