MySQL 两个Mysql表中的两列求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6465949/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum Two Columns in Two Mysql Tables
提问by user813801
I've been searching everywhere for this but no cigar. Smoke is starting to come out of my ears. please help
我一直在到处寻找这个，但没有雪茄。我的耳朵开始冒烟了。请帮忙
How do you sum two columns in two tables and group by userid?
您如何将两个表中的两列相加并按用户 ID 分组？
Have two tables.
有两张桌子。
Recipe Table
recipeid userid recipe_num_views
Meals Table
mealsid userid meal_num_views
Goal is to sum the num views in both tables and group by userid
目标是按用户 ID 对两个表和组中的 num 视图求和
so for example
Recipe Table
1 3 4
2 4 6
Meal Table
1 3 2
2 4 5
select sum(recipe views)+sum(meal views)
WHERE recipe.userid=meals.userid GROUP BY userid
should give
应该给
userid=3 , sum=6
userid=4, sum=11
this gives a much bigger number.
这给出了一个更大的数字。
回答by manji
SELECT recipe.userid, sum(recipe_num_views+meal_num_views)
FROM Recipe JOIN Meals ON recipe.userid=meals.userid
GROUP BY recipe.userid
EDIT:
编辑：
OK, from your comments, I understand that when you have for user 3: 4 recipes& 3 mealsyou will get the sum of the combination of all these rows => sum(recipes)*3 + sum(meals)*4
OK，从您的意见，我明白，当你有用户3：4周的食谱和3餐，您将得到所有这些行的组合的总和=>sum(recipes)*3 + sum(meals)*4
Try this query instead:
试试这个查询：
select r.userid, (sum_recipe + sum_meal) sum_all
FROM
(select userid, sum(recipe_num_views) sum_recipe
FROM Recipe
GROUP BY userid) r
JOIN (
select userid, sum(meal_num_views) sum_meal
FROM Meals
GROUP BY userid) m ON r.userid = m.userid
回答by Johan
If you're selecting from 2 tables you need to join them. Otherwise MySQL will not know how to link up the two tables.
如果您要从 2 个表中进行选择，则需要加入它们。否则 MySQL 将不知道如何链接这两个表。
select sum(recipe_num_views + meal_num_views)
from recipe r
inner join meals m ON (r.user_id = m.user_id)
group by m.user_id
See:
http://dev.mysql.com/doc/refman/5.5/en/join.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
请参阅：
http: //dev.mysql.com/doc/refman/5.5/en/join.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins .html
