将一列的SUM从一个表插入到JAVA中另一个表的另一列

I have F_table and S_table in my database.I want to insert the sum of all of the values of the Quantity_column of my F_table into Total_column of the S_table. For doing that what I have to write in JAVA?

我的数据库中有F_table和S_table。我想将我的F_table的Quantity_column的所有值的总和插入到S_table的Total_column中。为了做到这一点,我必须在JAVA写?

Suppose, I have two table one is Product_table and another is Stock_table.In the Product_table there are Columns like ID,Name,Quantity. And in the Stock_table there columns like ID,TotalProduct. Now I want the sum of all of the raw of Quantity Column of Product_table and insert it into the TotalProduct Column of Stock_table. When each time I add any product and its quantity then the TotalProduct will increase automatically. For getting that what I have to do in JAVA?

假设,我有两个表一个是Product_table,另一个是Stock_table。在Product_table中有列,如ID,Name,Quantity。在Stock_table中有ID,TotalProduct等列。现在我想要Product_table的所有原始数量列的总和,并将其插入Stock_table的TotalProduct列。每次添加任何产品及其数量时,TotalProduct都会自动增加。为了获得我在JAVA中必须做的事情?

Sorry if it is a newbie type question. I am actually a beginner. It will be great if anyone help me.

对不起,如果是新手型问题。我其实是初学者。如果有人帮助我会很棒。

1 个解决方案

#1

If I'm understanding correctly you want to get the total quantity for each ID from the product table, and insert those into the stock table, yes?

如果我理解正确,您想从产品表中获取每个ID的总数量,并将其插入到库存表中,是吗?

The below will get you the sum of the quantity, grouped by the id:

以下将为您提供数量的总和,按ID分组:

select id, sum(quantity) from product_table group by id;

So if you have :

所以如果你有:

1, test1, 5
1, test2, 10
2, test3, 20
2, test4, 1
3, test5, 5

you would get something like:

你会得到类似的东西:

1, 15
2, 21
3, 5

Then just insert these into the correct table.

然后将这些插入到正确的表中。

To combine the two you should be able to do something like:

要结合这两者,你应该能够做到这样的事情:

insert into stock_table 
select id, sum(quantity) from product_table group by id;

Keep in mind this is entirely an SQL problem that you're asking about. But to make it jdbc/java related, the jdbc for something like this would be:

请记住,这完全是您要问的SQL问题。但是为了使它与jdbc / java相关,这样的jdbc将是:

String sql = "insert into stock_table select id, sum(quantity) from product_table group by id";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.executeUpdate();

stmt.close();

You would probably want to check the number of rows updated/inserted by stmt.executeUpdate().

您可能希望检查stmt.executeUpdate()更新/插入的行数。

#1