如何用多个条件连接多个表

Question

我有六张桌子

online_transaction

| date | id | supplier_id | product code |

online_transaction_enc

| date | id | item |

offline_transaction

| date | id |

offline_transaction_enc

| date | id | item |

产品

| type | product_code |

供应商

| supplier_id | country |

选择日期为“2018-6月-01”和“2018-7月-30”之间的计数(项目)，类型=“家庭”和国家=“马来西亚”

这大概是我想要达到的目标。我想结合项目从线上和离线日期和id，所以我将得到所有的项目合并，然后跟着其他的要求。如何在MySQL中做到这一点？

Answer 1

试试这个：

 select count(a.item) from 
    (select date, id, item from online_transaction_enc
    union 
    select date, id, item from offline_transaction_enc)a
    inner join 
    (
    select  date,id,supplier_id,productcode from online_transaction
    union 
    select  date,id,supplier_id,productcode from offline_transaction)b
    on a.date=b.date and a.id=b.id
    inner join supplier on b.supplier_id=supplier.supplier_id
    inner join products on b.productcode=products.product_code
    where a.date between '20180601' And '20180730' AND Type='household' AND country='Malaysia'

Answer 2

SELECT count(item)
FROM online_transaction_enc
INNER JOIN online_transaction_enc ON supplier_id
INNER JOIN products ON supplier_id
...
WHERE WHERE (date BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
;

此解决方案使用内部连接。我相信这就是你要找的。下面是更多的文档http://www.mysqltutorial.org/mysql-inner-join.aspx