插入新行，并从其他行计算得出的数据 [英] Insert new row with data computed from other rows

问题描述

假设我有一个名为MyTable的MySQL表，如下所示:

Suppose I have a MySQL table called MyTable, that looks like this:

+----+------+-------+
| Id | Type | Value |
+----+------+-------+
|  0 | A    |     1 |
|  0 | B    |     1 |
|  1 | A    |     2 |
|  1 | B    |     3 |
|  2 | A    |     5 |
|  2 | B    |     8 |
+----+------+-------+

然后，对于每个Id，我想插入一个类型为C的新行，其Value是同一Id的行的A和B值的总和.此表上的主键是(Id, Type)，因此没有重复的Id，Type对的问题.

And, for each Id, I want to insert a new row with type C whose Value is the sum of the type A and B values for the rows of the same Id. The primary key on this table is (Id, Type), so there's no question of duplication of Id,Type pairs.

我可以通过此查询创建所需的行:

I can create the rows I want with this query:

SELECT MyTable_A.Id AS Id, 'C' AS Type, (A_Val + B_Val) AS Value FROM 
       (SELECT Id, Value AS A_Val FROM MyTable WHERE Type='A') AS MyTable_A
  JOIN (SELECT Id, Value AS B_Val FROM MyTable WHERE Type='B') AS MyTable_B
    ON MyTable_A.Id = MyTable_B.Id

给予:

+----+------+-------+
| Id | Type | Value |
+----+------+-------+
|  0 | C    |     2 |
|  1 | C    |     5 |
|  2 | C    |    13 |
+----+------+-------+

但是问题是:如何使用此结果将生成的type- C行插入到MyTable中?

But the question is: How do I use this result to insert the generated type-C rows into MyTable?

是否有相对简单的方法来执行查询，还是需要编写存储过程?而且如果是后者，则指导会有所帮助，因为我不太了解它们.

Is there a relatively simple way to do this with a query, or do I need to write a stored procedure? And if the latter, guidance would be helpful, as I'm not too well versed in them.

推荐答案

您只需将select(在您不需要as子句"中稍作修改)附加到插入内容上即可.例如:

You can just append that select (slightly modified as in "you don't need the as clauses") onto an insert. For example:

insert into MyTable (Id,Type,Value)
    select MyTable_A.Id, 'C', (A_Val + B_Val) from ...

假设您的查询实际上是正确的-我对此不做任何评估:-)

assuming that your query is actually correct - I make no evaluation of that :-)

通过进一步的示例，

insert into MyTable (Id,Type,Value)
    select Id+1000, 'C', Value from MyTable where Type = 'A'

将添加以下行:

+------+------+-------+
| Id   | Type | Value |
+------+------+-------+
| 1000 | C    |     1 |
| 1001 | C    |     2 |
| 1002 | C    |     5 |
+------+------+-------+

这篇关于插入新行，并从其他行计算得出的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！