mysql - 在一个mysql查询中使用join group by从多个表中选择数据

Question

表1法案

| bill_id| date      | supplier_id |amount|
| 101    | 2019-03-16| 1570        |1000  |
| 102    | 2019-05-20| 1570        |2500  |

表2付款方式

| bill_id| date       | paid|
| 101    | 2019-03-17 | 800   |
| 101    | 2019-05-20 | 150   |

预期结果

| supplier_id |bill_id |amount|Paid | Balance|
| 1570        | 101    |1000  | 950 | 50     |
| 1570        | 102    |2500  | 0   | 2500   |

我如何实现这个目标

Answer 1

我会预先聚合付款

SELECT p.bill_id
     , SUM(p.amount) AS paid
  FROM payment p 
 GROUP BY p.bill_id

然后将该查询用作内联 View ...

SELECT b.supplier_id
     , b.bill_id
     , b.amount
     , IFNULL(q.paid,0)            AS paid 
     , b.amount - IFNULL(q.paid.0) AS balance  
  FROM bill b
  LEFT
  JOIN ( SELECT p.bill_id
              , SUM(p.amount) AS paid
           FROM payment p 
          GROUP BY p.bill_id
       ) q
    ON q.bill_id = b.bill_id
 ORDER
    BY b.supplier_id
     , b.bill_id

<小时/>

请注意，我们无法保证 bill 表中 bill_id 的唯一性。看起来它可能是主键，但这只是猜测。如果这不是唯一的，那么该查询的结果可能不是我们想要的。

请注意，我们使用的是外连接(LEFT 关键字)，因此查询将返回 bill 的行，而 payment 中没有匹配的行。 IFNULL 包装器是将 NULL 值(如果付款中没有匹配行，我们将得到该值)转换为零的简写方式。