本人想在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结果居然都是所需要的二倍.
请高手指教如何解决. 谢谢!
/* 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结果居然都是所需要的二倍.
请高手指教如何解决. 谢谢!
这是一种治标不治本的方法,仅供参考。(*^__^*)
但是有时有的字段只有一个值, 比如某个id只有一个结果, 比如说sum(5)=5, 这时候就无法/2了.
请问还有什么其他的方法吗
谢谢了.
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这样不行?