对两个Mysql表中的两列求和 [英] Sum Two Columns in Two Mysql Tables

问题描述

我一直在到处寻找这个，但没有雪茄.烟开始从我的耳朵里冒出来.请帮助

I've been searching everywhere for this but no cigar. Smoke is starting to come out of my ears. please help

如何汇总两个表中的两列并按用户ID分组?

How do you sum two columns in two tables and group by userid?

有两个表.

Recipe Table
recipeid   userid   recipe_num_views

Meals Table
mealsid   userid    meal_num_views 

目标是汇总两个表中的num个视图，并按用户ID分组

Goal is to sum the num views in both tables and group by userid

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

应该给

userid=3 , sum=6
userid=4, sum=11

这个数字更大.

推荐答案

SELECT recipe.userid, sum(recipe_num_views+meal_num_views) 
FROM Recipe JOIN Meals ON recipe.userid=meals.userid
GROUP BY recipe.userid

好的，从您的评论中我们了解到，当您拥有用户3 时: 4个食谱& 三餐，您将获得所有这些行的总和=> sum(recipes)*3 + sum(meals)*4

OK, from your comments, I understand that when you have for user 3: 4 recipes & 3 meals you will get the sum of the combination of all these rows => sum(recipes)*3 + sum(meals)*4

改为尝试以下查询:

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

这篇关于对两个Mysql表中的两列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！