如何将SELECT SUM中的值插入到其他表中？

I'm stuck and I could use a little input. How can I insert the SUM(amount) of all values of "accountKonto" of table "Buchung" into one row of the table "Erfolg"?

我被卡住了,我可以使用一点输入。如何将表“Buchung”的“accountKonto”的所有值的SUM(金额)插入“Erfolg”表的一行?

"Buchung": id accountKonto amount

“Buchung”:id accountKonto金额

"Erfolg": id totalAmountAccountKonto1 totalAmountAccountKonto2 …

“Erfolg”:id totalAmountAccountKonto1 totalAmountAccountKonto2 ...

For each possible "accountKonto" in "Buchung", there is one column in "Erfolg", into which I need to insert the sum. At the end, I need to have one new row in "Erfolg" that should have all sums of "amount" for each "accountKonto" that exists in "Buchung". Makes sense?

对于“Buchung”中每个可能的“accountKonto”,“Erfolg”中有一列,我需要在其中插入总和。最后,我需要在“Erfolg”中添加一个新行,该行应该具有“Buchung”中存在的每个“accountKonto”的所有“金额”总和。说得通?

It should begin like this:

它应该像这样开始:

SELECT SUM(amount) FROM Buchung …

But how do I tell it to put each sum into the corresponding field of table Erfolg?

但是如何告诉它将每个总和放入表Erfolg的相应字段中?

Thanks a lot for your help!

非常感谢你的帮助!

Gary

3 个解决方案

#1

You should combine INSERT .. SELECT with PIVOT.

你应该将INSERT .. SELECT与PIVOT结合起来。

Using PIVOT (available in SQL Server and Oracle, only):

SELECT *
FROM (
  SELECT accountKonto, amount
  FROM Buchung
) t
PIVOT (
  SUM(amount) FOR accountKonto IN ([1], [2], [3])
) AS p

The above query produces something like:

上面的查询产生如下内容:

1      2      3
---------------------
28.00  17.00  15.35

If you're not using SQL Server:

... then you cannot use PIVOT, but you can emulate it easily:

...那么你不能使用PIVOT,但你可以轻松地模仿它:

SELECT
  SUM(CASE accountKonto WHEN 1 THEN amount END) totalAmountAccountKonto1,
  SUM(CASE accountKonto WHEN 2 THEN amount END) totalAmountAccountKonto2,
  SUM(CASE accountKonto WHEN 3 THEN amount END) totalAmountAccountKonto3
FROM Buchung

Inserting that into your other table:

Just use INSERT .. SELECT as follows:

只需使用INSERT .. SELECT如下:

INSERT INTO Erfolg (
  totalAmountAccountKonto1,
  totalAmountAccountKonto2,
  totalAmountAccountKonto3
)
SELECT p.[1], p.[2], p.[3]
FROM (
  SELECT accountKonto, amount
  FROM Buchung
) t
PIVOT (
  SUM(amount) FOR accountKonto IN ([1], [2], [3])
) AS p;

... or if PIVOT is not available:

......或者如果没有PIVOT:

INSERT INTO Erfolg (
  totalAmountAccountKonto1,
  totalAmountAccountKonto2,
  totalAmountAccountKonto3
)
SELECT
  SUM(CASE accountKonto WHEN 1 THEN amount END) AS totalAmountAccountKonto1,
  SUM(CASE accountKonto WHEN 2 THEN amount END) AS totalAmountAccountKonto2,
  SUM(CASE accountKonto WHEN 3 THEN amount END) AS totalAmountAccountKonto3
FROM Buchung

#2

The design you are using is not very good - you run out of columns pretty fast (for MySQL - approx. 4k columns, depends a bit).

您使用的设计不是很好 - 您的列快速耗尽(对于MySQL - 大约4k列,取决于一点)。

I'd use design for aggregate table, similar to:

我使用聚合表的设计,类似于:

iteration | accountNr | sum

Now, to fill the table, you just do (replace 1 with some timestamp, iteration ID, link to some aggregations table, your choice):

现在,为了填充表,你只需要做(用一些时间戳,迭代ID替换1,链接到某些聚合表,你的选择):

INSERT INTO aggregate (iteration, accountNr, sum) SELECT 1, accountNr, SUM(amount) FROM data GROUP BY accountNr

Now that you have data for each iteration, you can work on it in interfaces or some pivot tables just as you wish.

现在您已经拥有了每次迭代的数据,您可以根据需要在接口或某些数据透视表中处理它。

#3

Check Pivot keyword from your db server document. If it's not supported, you can do it in your code. Or you have to do multiple queries.

从数据库服务器文档中检查Pivot关键字。如果不支持,您可以在代码中执行此操作。或者你必须做多个查询。