php - MySQL 加入两个表给出了不正确的结果

Question

我正在尝试加入两个查询，希望能得到与我在单个查询中收到的结果相同的结果。我绝不擅长 MySQL 连接查询，因此我的困境。这是我的查询及其结果。

这是查询#1:

select      sum(fbaoh.qty) as sumQty 
from        FBAOrderHistory fbaoh 
where       fbaoh.asin = 'B002BRSCJ6'
and         fbaoh.sku = '643356041428'
    and         fbaoh.account_id = 8
and         fbaoh.datetimePlaced BETWEEN '2014-05-12' AND '2014-06-11';
/*
sumQty = 139
*/

这是查询#2:

select      count(fbai.id) as totalRows
from        FBAInventory fbai 
LEFT JOIN       FBAInventoryReport fbair
ON          fbai.fbaInventoryReport_id = fbair.id
where       fbai.asin = 'B002BRSCJ6'
and     fbai.sku = '643356041428'
    and         fbai.account_id = 8         
and         fbair.report_datetime BETWEEN '2014-05-12' AND '2014-06-11';
/*
totalRows = 30
*/

查询 #3 - 以下是我的查询:

select      sum(fbaoh.qty) as sumQty, 
            count(fbai.id) as totalRows
from        FBAOrderHistory fbaoh 
LEFT JOIN   FBAInventory fbai
 ON         fbaoh.asin=fbai.asin
LEFT JOIN   FBAInventoryReport fbair
 ON             fbai.FBAInventoryReport_id=fbair.id
where       fbaoh.asin = 'B002BRSCJ6'
and         fbaoh.sku = '643356041428'
    and         fbai.account_id = 8
and         fbaoh.account_id = 8        
and         fbaoh.datetimePlaced BETWEEN '2014-05-12' AND '2014-06-11'
and         fbair.report_datetime BETWEEN '2014-05-12' AND '2014-06-11';
/*
sumQty = 4170
totalRows = 3840
*/

这里是表模式:

亚马逊物流订单历史记录

 id | qty | sku     | asin    | datetimePlaced
 ------------------------------
 1  | 1   | ABC     | 123     | 2014-05-20 06:06:03

亚马逊物流库存

 id | sku     | asin    | fbaInventoryReport_id
 ---------------------------------------------------
 1  | ABC     | 123     | 1

亚马逊物流库存报告

 id | report_datetime
 ---------------------------------------------------
 1  | 2014-05-20 06:06:03

查询 #1 - 我根据 sku 和 asin 以及日期范围获取总和。

查询 #2 - 我正在获取基于 sku 和 asin 以及日期范围的总行数。

查询 #3 - 我想得到相同的结果。两个查询之间的唯一联系是 sku 和 asin。

显然，上次查询的结果不是我想要接收的结果。我究竟做错了什么？

这是我能说的。查询不只是向我显示 sumQty 和 totalRows，而是实际将 sumQty (139) 和 totalRows (30) 相乘，从而等于:4170。至于 3840，我不知道这是如何呈现的。

感谢任何人可以提供给我的帮助!

Answer 1

无法连接这些查询，因为它们会像您发现的那样丢弃总和和计数。原因是这些表之间没有一对一的关系。执行此操作的最佳方法是按如下方式加入结果:

select q1.sumQty, q2.totalRows
from(
select      sum(fbaoh.qty) as sumQty 
from        FBAOrderHistory fbaoh 
where       fbaoh.asin = 'B002BRSCJ6'
and         fbaoh.sku = '643356041428'
    and         fbaoh.account_id = 8
and         fbaoh.datetimePlaced BETWEEN '2014-05-12' AND '2014-06-11') q1,
(select      count(fbai.id) as totalRows
from        FBAInventory fbai 
LEFT JOIN       FBAInventoryReport fbair
ON          fbai.fbaInventoryReport_id = fbair.id
where       fbai.asin = 'B002BRSCJ6'
and     fbai.sku = '643356041428'
    and         fbai.account_id = 8         
and         fbair.report_datetime BETWEEN '2014-05-12' AND '2014-06-11') q2