合并来自不同表的列并分配给新表

I am working on a small project, where I have 2 different tables, which shows the inventory count of users A and B. Consider the table names to be the same. In each table, I have a column called count, which indicates the respective inventory count of each user. So:

我正在开发一个小项目，我有两个不同的表，显示用户A和B的库存数。考虑表名相同。在每个表中，我有一个名为count的列，它指示每个用户的相应库存计数。所以：

A.item | A.count     B.item | B.count     C.item | c.count

   XYZ | 25             XYZ | 31             XYZ | 0

I have a third table C, which has an empty count at the moment. As you can see, the item name (or an id) is common for all 3 tables. What I want to do, is to add the count for users A and B, and then assign it to C. So, what I think I should be doing is;

我有一个第三个表C，目前有空数。如您所见，项目名称（或ID）对于所有3个表都是通用的。我想要做的是为用户A和B添加计数，然后将其分配给C.所以，我认为我应该做的是;

UPDATE C set count = A.count + B.count WHERE A.item = B.item

Obviously the above syntax doesn't work. The only thing I've managed to get going so far, is to just show the respective counts from both the table, by using the following code:

显然上面的语法不起作用。到目前为止，我唯一能够实现的目的是通过使用以下代码显示两个表中的相应计数：

SELECT A.count, B.count
FROM A
INNER JOIN B ON A.item = B.id
LIMIT 0 , 30

But with the above code, I don't know how to proceed, so that I can sum the counts from A and B, then assign it to C.count. I tried using a while loop in php, and going through each count row by row - querying over and over, but it takes a long time, and it usually times out, based on the default php timeout setting.

但是使用上面的代码，我不知道如何继续，因此我可以将A和B中的计数相加，然后将其分配给C.count。我尝试在php中使用while循环，并逐行遍历每个计数 - 一遍又一遍地查询，但它需要很长时间，并且它通常会超时，基于默认的php超时设置。

Any help would really be appreciated.

真的很感激任何帮助。

Edit: The above question has been clearly answered by Tim. I'm wondering, how do I modify Tim's code, so that, instead of the count, now I have 2 columns with strings. So:

编辑：蒂姆已经清楚地回答了上述问题。我想知道，我如何修改Tim的代码，因此，现在我有两列字符串，而不是计数。所以：

A.item | A.comment     B.item | B.comment     C.item | c.comment

   XYZ | 25               XYZ | 31               XYZ | 0

How do I modify Tim's code to take the comments from A and B, and add them to C? I tried the following:

如何修改Tim的代码以获取A和B中的注释，并将它们添加到C？我尝试了以下方法：

UPDATE C c
LEFT JOIN A a
    ON a.item = c.item
LEFT JOIN B b
    ON b.item = c.item
SET c.comment = COALESCE(a.comment, 0) + COALESCE(b.comment, 0)

But it doesn't seem to be this straight forward. I tried looking up the documentation for COALESCE, but i was not able to relate it to this issue I'mm having now, with string. Any help?

但它似乎并不是那么直截了当。我试着查看COALESCE的文档，但我现在无法将它与这个问题联系起来。有帮助吗？

2 个解决方案

#1

Join the three tables together by item, and then update the count of C as being the sum of the other two.

按项加入三个表，然后将C的计数更新为另外两个的总和。

UPDATE C c
LEFT JOIN A a
    ON a.item = c.item
LEFT JOIN B b
    ON b.item = c.item
SET c.count = COALESCE(a.count, 0) + COALESCE(b.count, 0)

I used left join only in the above update, in case a given item in C have only a count in A but not B, or vice-versa. If you instead want the count to be assigned only if both A and B have counts, then replace the left joins with inner joins.

我只在上面的更新中使用了左连接，以防C中的给定项目只有A但不是B的计数，反之亦然。如果您只想在A和B都有计数的情况下分配计数，那么用内部连接替换左连接。

#2

Actually, following on from Tim's code, this code solves my updated question - i.e. joining strings.

实际上，继Tim的代码之后，这段代码解决了我更新的问题 - 即加入字符串。

UPDATE C c
LEFT JOIN A a
    ON a.item = c.item
LEFT JOIN B b
    ON b.item = c.item
SET c.comment = CONCAT(c.comment, a.comment, b.comment);

#1