如何将多对多结合起来

如何将多对多结合起来

表格:

cust table:
   cust_id, name, etc

bill table:
   bill_id, cust_id, amt, due_date, status (open/closed)

payment table:
   payment_id, bill_id, amt etc


客户可以通过分期付款来结算一张账单。因此,一个bill_id可能与payment_ids有关。

我无法生成此记录集:



cust_id |到期时间



“ due amt”是所有bill.amts的总和-所有pay.amts的总和,并且状态为打开。

帐单表

bill_id cust_id     amt     status
1       18          200     open
2       18          200     open
3       17          200     open
4       17          200     open
5       17          200     open
6       17          200     closed


付款表

payment_id  bill_id cust_id amt
1           1       18      50
2           2       18      40
3           3       17      10


预期产量

cust_id     due_amt         hint/how
17          590             (600-10) .. 600 -> because one is closed
18          310             (400-(50+40))

最佳答案

select c.cust_id, sum(b.amt) - sum(p.amt) as due_amt
from cust c
left join bill b on b.cust_id = c.cust_id and b.status = 'open'
left join payment p on p.bill_id = b.bill_id
group by c.cust_id


SQLFiddle demo

关于mysql - 如何将多对多结合起来?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/18291185/

10-13 04:48