mysql - 超过 2 个表的内连接

Question

我有 3 个不同的表余额、已收到、费用，其中包含以下数据。收到表:

mysql> select * from received;
+-----+---------+-----------------+---------------------+
| rid | site_id | received_amount | receive_date        |
+-----+---------+-----------------+---------------------+
|   1 |       1 |             500 | 2015-08-19 18:16:51 |
|   2 |       1 |             600 | 2015-08-19 18:16:52 |                                                                                                                                       
|   3 |       1 |             500 | 2015-08-20 18:16:52 |                                                                                                                                       
|   4 |       1 |             500 | 2015-08-19 18:16:52 |
+-----+---------+-----------------+---------------------+

餐 table 费用:

mysql> select * from expenses;
+-----+---------+----------------+---------------------+
| eid | site_id | expense_amount | expense_date        |
+-----+---------+----------------+---------------------+
|   1 |       1 |            500 | 2015-08-19 18:17:11 |
+-----+---------+----------------+---------------------+

表余额:

mysql> select * from balance;
+----------------+---------+---------------+--------------+-----------------+-----------------+------+------+---------------------+
| transaction_id | site_id | account_title | particulars  | opening_balance | closing_balance | rid  | eid  | transaction_date    |
+----------------+---------+---------------+--------------+-----------------+-----------------+------+------+---------------------+
|              1 |       1 | test1         | test1 values |               0 |             500 |    1 | NULL | 2015-08-19 18:16:51 |
|              2 |       1 | test1         | test1 values |             500 |            1100 |    2 | NULL | 2015-08-19 18:16:52 |
|              3 |       1 | test1         | test1 values |            1100 |            1600 |    3 | NULL | 2015-08-20 18:16:52 |
|              4 |       1 | test1         | test1 values |            1100 |            1600 |    4 | NULL | 2015-08-19 18:16:52 |
|              5 |       1 | test1         | test1 values |            1600 |            1100 | NULL |    1 | 2015-08-19 18:17:11 |
+----------------+---------+---------------+--------------+-----------------+-----------------+------+------+---------------------+

我正在尝试使用以下查询将收到的金额和费用合并到余额表中，但不知何故我无法获得正确的方法来获取它。

select 
      b.transaction_id,
      b.site_id,
      b.account_title,
      b.particulars,
      b.opening_balance,
      r.received_amount,
      e.expense_amount,
      b.closing_balance,
      b.transaction_date 
   from 
      balance b 
         inner join received r 
            on b.site_id = r.site_id 
         inner join expenses e 
            on b.site_id = e.site_id 
   group by 
      b.transaction_id;

我正在尝试获取此输出

+----------------+---------+---------------+--------------+-----------------+-----------------+----------------+-----------------+---------------------+
| transaction_id | site_id | account_title | particulars  | opening_balance | received_amount | expense_amount | closing_balance | transaction_date    |
+----------------+---------+---------------+--------------+-----------------+-----------------+----------------+-----------------+---------------------+
|              1 |       1 | test1         | test1 values |               0 |             500 |            NULL |             500 | 2015-08-19 18:16:51 |
|              2 |       1 | test1         | test1 values |             500 |             600 |            NULL |            1100 | 2015-08-19 18:16:52 |
|              3 |       1 | test1         | test1 values |            1100 |             500 |            NULL |            1600 | 2015-08-20 18:16:52 |
|              4 |       1 | test1         | test1 values |            1600 |             NULL |            500 |            1100 | 2015-08-19 18:16:52 |
|              5 |       1 | test1         | test1 values |            1100 |             500 |            NULL |            1600 | 2015-08-19 18:17:11 |
+----------------+---------+---------------+--------------+-----------------+-----------------+----------------+-----------------+---------------------+

Answer 1

你们有点接近了。您需要对收据和费用执行 LEFT-JOIN，而不是 INNER JOIN。由于您的“余额”表包含所有事务，因此它将驱动它在基础支持表中看到的条目。另外，不要只加入站点 ID，而只加入“rID”和“eID”，因为无论如何它们都是表上的主键。

无需按交易 ID 进行分组，因为它是余额表中的主键，并且给定 ID 只有一个条目。

select 
      b.transaction_id,
      b.site_id,
      b.account_title,
      b.particulars,
      b.opening_balance,
      r.received_amount,
      e.expense_amount,
      b.closing_balance,
      b.transaction_date 
   from 
      balance b 
         LEFT join received r 
            on b.rid = r.rid
            AND b.site_id = r.site_id 
         LEFT join expenses e 
            on b.eid = e.eid
            AND b.site_id = e.site_id 
   order by
      b.site_id,
      b.transaction_date