SQL SUM GROUP BY 两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15109109/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL SUM GROUP BY two tables
提问by Sjrsmile
I'm having difficulty writing an SQL query that will correctly group account_no together and subtracting an amount.
我在编写 SQL 查询时遇到了困难，该查询将正确地将 account_no 组合在一起并减去一个金额。
Firstly I wrote this query which updates everything fine except ACCOUNT_NO A-102 should end up as 4500 not as two different correct balances.
首先我写了这个查询，它更新了一切，除了 ACCOUNT_NO A-102 最终应该是 4500，而不是两个不同的正确余额。
select transactions.account_no, account.balance, transactions.amount,
(account.balance + transactions.amount) AS "CORRECT BALANCE"
from transactions, account
where account.account_no = transactions.account_no;
ACCOUNT_NO| BALANCE | AMOUNT | CORRECTBALANCE
A-102 | 4000 | 2000 | 6000
A-102 | 4000 | -1500 | 2500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
I tried to sum and group by the account_no but I cannot work out how to do this with the two tables. This was just something I tried but could not get to work.
我试图通过 account_no 进行求和和分组，但我无法弄清楚如何用这两个表来做到这一点。这只是我尝试过但无法开始工作的事情。
select transactions.account_no, SUM(transactions.amount)
from transactions
group by transactions.account_no;
ACCOUNT_NO| SUM(TRANSACTIONS.AMOUNT)
A-305 | 1300
A-102 | 500
A-222 | -1000
The expected outcome should be:
预期的结果应该是：
ACCOUNT_NO| BALANCE | AMOUNT | CORRECTBALANCE
A-102 | 4000 | 500 | 4500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
This is because the account A-102 it has two different amounts coming out, but from the same balance.
这是因为帐户 A-102 它有两个不同的金额，但来自相同的余额。
回答by Josien
For your query, to get the two rows grouped on one row, you can try grouping on the account number AND the balance:
对于您的查询，要将两行分组为一行，您可以尝试对帐号和余额进行分组：
SELECT T.account_no
,A.balance
,SUM(T.amount) AS TotalAmount
,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM transactions AS T
INNER JOIN account AS A ON T.account_no = A.account_no
GROUP BY T.account_no, A.balance;
(By the way, I've used the ANSI join instead of the 'old' way of joining tables, because it's much more clear what you're doing that way.)
（顺便说一下，我使用了 ANSI 连接而不是连接表的“旧”方式，因为这样更清楚你在做什么。）
EDIT
编辑
To make things a bit more clear, I've made a SQL Fiddle. Does this represent your situation more or less correctly?
为了让事情更清楚一点，我制作了一个SQL Fiddle。这或多或少正确地代表了您的情况？
EDIT2
编辑2
The above query would not show any accounts without transactions, as Kafcommented. That might be what you want, but in case it's not you can switch the join tables like this:
正如Kaf评论的那样，上述查询不会显示任何没有交易的账户。这可能是您想要的，但如果不是，您可以像这样切换连接表：
SELECT A.account_no
,A.balance
,SUM(T.amount) AS TotalAmount
,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM account AS A
LEFT OUTER JOIN transactions AS T ON T.account_no = A.account_no
GROUP BY A.account_no, A.balance;
回答by Kaf
Are you looking how to join tables and sum using group by?
您是否正在寻找如何使用 group by 连接表和求和？
First query;
第一个查询；
UPDATE:I think your Account tablehas 1:manyrelationship with Transaction table, so, you should get the sum from transaction tableand then joinwith Account. Ideally, you need a left joinas below.
更新：我认为您Account table与1:many 的关系Transaction table，因此，您应该get the sum from transaction table然后加入与Account. 理想情况下，您需要left join如下。
select a.account_no,
a.balance,
isnull(x.amount,0) amount,
(a.balance + isnull(x.amount,0)) correctAmount
from account a left join (
select t.account_no, sum(t.amount) amount
from transactions t
group by t.account_no ) x
on a.account_no = x.account_no
SQL-FIDDLE-DEMO(thanks @Josien for tables and data)
SQL-FIDDLE-DEMO（感谢@Josien 提供表格和数据）
