查询如何SUM连接表

Question

我不知道标题应该怎么写。思考题目比写这个问题要花更多的时间。
直奔主题
假设我有三个表:

//table customers 
|  ID  | Name                  |
++++++++++++++++++++++++++++++++
| 194  | PT Comro Haneut       |
| 195  | PT Kareueut Kameumeut |
//table customer's savings 
|  ID  | IDCustomer | SavingsAmount |
+++++++++++++++++++++++++++++++++++++
|   1  |    194     |    5000000    |
|   2  |    195     |     250000    |
|   3  |    195     |    2500000    |
|   4  |    194     |     125000    |
|   5  |    194     |     175000    |
//table transactions
|  ID  | IDCustomer | Amount        |
+++++++++++++++++++++++++++++++++++++
|  1   |    195     |    1000000    |
|  2   |    195     |     250000    |
|  3   |    194     |    3500000    |
|  4   |    194     |     300000    |

我想把储蓄金额和交易金额相加，并把结果写成一行，像这样:

// expected result of the query
| IDCustomer | Savings      | Transactions  | Balance       |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|   194      |    5300000   |   4800000     |    500000     |
|   195      |    2750000   |   1250000     |   1500000     |

我尝试自己构建查询，但我总是失败。我得到了存款和交易总额翻了一番。

有谁能帮忙吗?

Answer 1

查询

select svg.id, Savings, Transactions, (Savings - Transactions) as Balance
from
(
  select c.id as id, sum(s.SavingsAmount) as Savings
  from customers c
  inner join savings s
  on c.id=s.idcustomer
  group by c.id
) svg
inner join
(
  select c.id as id, sum(t.amount) as Transactions
  from customers c
  inner join transactions t
  on c.id=t.idcustomer
  group by c.id
) trans
on svg.id = trans.id
;

设置>

create table customers
(
  id integer primary key not null,
  name varchar(23) not null
);
create table savings
(
  id integer primary key not null,
  IDCustomer integer not null,
  SavingsAmount decimal(10, 2) not null,
  foreign key ( IDCustomer ) references customers ( id )
);
create table transactions
(
  id integer primary key not null,
  IDCustomer integer not null,
  amount decimal(10, 2) not null,
  foreign key ( IDCustomer ) references customers ( id )
);
insert into customers
( id, name )
values
( 194  , 'PT Comro Haneut'       ),
( 195  , 'PT Kareueut Kameumeut' )
;
insert into savings 
(  id  , IDCustomer , SavingsAmount )
values
(   1  ,    194     ,    5000000    ),
(   2  ,    195     ,     250000    ),
(   3  ,    195     ,    2500000    ),
(   4  ,    194     ,     125000    ),
(   5  ,    194     ,     175000    )
;
insert into transactions
(  id  , IDCustomer , amount        )
values
(  1   ,    195     ,    1000000    ),
(  2   ,    195     ,     250000    ),
(  3   ,    194     ,    3500000    ),
(  4   ,    194     ,     300000    )
;

---

+-----+------------+--------------+------------+
| id  | Savings    | Transactions | Balance    |
+-----+------------+--------------+------------+
| 194 | 5300000.00 |   3800000.00 | 1500000.00 |
| 195 | 2750000.00 |   1250000.00 | 1500000.00 |
+-----+------------+--------------+------------+

sqlfiddle

Answer 2

看起来您不需要客户表中的任何特定数据，因此只需优化结果即可使用

SELECT savings.IDCustomer, sum(SavingsAmount) as savings,sum(Amount) as amount, sum(SavingsAmount)-sum(Amount) as Balance
FROM savings
LEFT JOIN transactions
ON savings.IDCustomer=transactions.IDCustomer
group by savings.IDCustomer
ORDER BY savings.IDCustomer;