SQL SUM GROUP BY 两张表 [英] SQL SUM GROUP BY two tables

问题描述

我很难编写一个 SQL 查询来正确地将 account_no 组合在一起并减去一个金额.

I'm having difficulty writing an SQL query that will correctly group account_no together and subtracting an amount.

首先我写了这个查询，它更新了一切正常，除了 ACCOUNT_NO A-102 应该以 4500 结束，而不是两个不同的正确余额.

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.

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    

我尝试按 account_no 求和和分组，但我不知道如何对这两个表执行此操作.这只是我尝试过但无法开始工作的事情.

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.

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   

预期的结果应该是:

ACCOUNT_NO| BALANCE   | AMOUNT    | CORRECTBALANCE 

A-102     |  4000     |  500      |  4500                 
A-222     |  8000     |  -1000    |  7000                   
A-305     |  2000     |  1300     |  3300    

这是因为帐户 A-102 有两个不同的金额流出，但来自相同的余额.

This is because the account A-102 it has two different amounts coming out, but from the same balance.

推荐答案

对于您的查询，要将两行分组为一行，您可以尝试按帐号和余额进行分组:

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;

(顺便说一句，我使用了 ANSI 连接而不是旧"连接表的方式，因为这样更清楚你在做什么.)

(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.)

编辑

为了让事情更清楚一点，我做了一个 SQL小提琴.这是否或多或少正确地代表了您的情况?

To make things a bit more clear, I've made a SQL Fiddle. Does this represent your situation more or less correctly?

EDIT2

正如 Kaf 评论的那样，上述查询不会显示任何没有交易的帐户.这可能是您想要的，但如果不是，您可以像这样切换连接表:

The above query would not show any accounts without transactions, as Kaf commented. That might be what you want, but in case it's not you can switch the join tables like this:

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;

这篇关于SQL SUM GROUP BY 两张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！