如何对两个不同表的两个不同列的数据求和？ [英] How to sum data of two different columns of two different tables?

问题描述

我的数据库中有两个表，其中每个表中都有两列，其中一列用于userid&一个是用于显示一些数字数据。

i have two tables i my database in which i have two columns in each table one is for userid & one is for showing some numeric data.

我只想添加数字列的值，但问题是两个表具有相同的已用id并不是强制性的。

I just want to add the value of the numeric column but the issue is that it's not compulsory that both the table have same used id.

我将尝试使用示例进行解释：

I will try to explain it using example:

table1

userid  score1

  1       200
  2       300


table2

userid  score2

  1       400

所以现在我要做的是求和根据用户ID形成这两个表，并将其显示在新表中，如下所示：

so now what i want to do is to sum the score form these two tables as per the user id and show it in a new table like this:

userid  score_sum

  1       600
  2       300

如果我只是使用

Select sum(table1.score1+table2.score2) 
FROM table1 JOIN table2 ON table1.id=table2.id

，那么它只会显示用户ID 1 的总和，因为 userid 2 在第二个表中不存在。 o请建议我如何解决此问题。

then it will only show the sum for userid 1 because userid 2 is not present in the second table.So please suggest me how to solve this issue.

推荐答案

通常，您可以通过执行完全外部联接来解决此问题，但是MySql

Normally you would solve this problem by doing a full outer join, but MySql does not support such joins so it needs to be faked.

SELECT SUM(COALESCE(table1.score1,0) + COALESCE(table2.score2,0))
FROM (
    SELECT DISTINCT(id) FROM (
        SELECT userid FROM table1 UNION SELECT userid FROM table2
    ) tmp
) userids
LEFT JOIN table2 ON table2.userid = userids.userid
LEFT JOIN table1 ON table1.userid = userids.userid
GROUP BY table1.userid

说明

首先，我们需要获取 table1 或 table2 中存在的所有用户ID的列表。 code>，因此我们可以将此列表用作数据集，以从中进行 LEFT OUTER JOIN 。从 table1 或 table2 开始联接的任何方法都不会削减它，因为如果所选表不包括某些用户ID X，那么该用户将根本不会出现在最终结果中。

Explanation

First of all we need to get a list of all the user ids that are present in either table1 or table2 so we can use this list as the dataset to do a LEFT OUTER JOIN from. Any approach that starts the join from table1 or table2 is not going to cut it because if the selected table does not include some user id X then that user will not appear at all in the final results.

已创建 userids 结果集通过子选择，我们 LEFT JOIN 两个有趣的表来获取每个用户的分数。如果两个表中都不存在用户，则该用户的得分为 NULL （因为 NULL +数字给出 NULL ），因此我们使用 COALESCE 转换任何 NULL 到 0 。

Having created the userids resultset through the subselect we LEFT JOIN both interesting tables to get the scores for each user. If a user is not present in both tables then we 're going to have a score of NULL for that user (because NULL + number gives NULL), so we use COALESCE to convert any NULL to 0.

我应该注意，无论哪个用户包含在（或不包含在）哪个表中，或表在查询中出现的顺序。

I should note that this query will work correctly irrespective of which user is included (or not) in which table, or of the order that the tables appear in the query.

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