mysql - 使用左外连接和 on 子句从具有公共(public)列的两个不同表中检索数据

Question

伙计们，我有两张不同的 table 。

表一:

contract-id | contract-date
  1         | 01-05-2016
  1         | 01-06-2016
  1         | 01-07-2016
  2         | 01-01-2016
  2         | 01-02-2016
  3         | 01-05-2016
  3         | 01-06-2016

表 2:

invoice-number|contract-id|invoice-from-date|invoice-to-date
 1            |1          |01-05-2016       |31-05-2016
 2            |1          |15-05-2016       |31-05-2016
 3            |1          |25-05-2016       |31-05-2016

我的要求是，我必须显示表 1 中当月的所有契约(Contract)，并且我必须显示表 2 中为这些契约(Contract)开具的发票数量。如果有任何发票，它应该显示它的计数或简单地显示为零。预期的答案是:

contract-id|counts
 1         |3
 3         |0

contract-id: 1 的计数是 3，因为我为当月开了三张发票，而 contract-id: 3 的计数是 0，因为我没有为此开过任何发票。 contract-id:2 丢失了，因为它不在当前月份。

这是我试过的:

    select cbm_contract_period_value_split.contract_id,
count(cbm_contract_invoice_request.contract_id),
cbm_contract_period_value_split.contract_date
from cbm_contract_period_value_split
left outer join cbm_contract_invoice_request
on cbm_contract_period_value_split.contract_id = cbm_contract_invoice_request.contract_id
and MONTH(cbm_contract_period_value_split.contract_date) = MONTH(CURDATE())
and MONTH(cbm_contract_invoice_request.invoice_for_the_period_of_from_date) = MONTH(CURDATE())

我已经发布了这个问题，但现在我发布的是经过编辑的版本，其中包含我得到的答案。然而，我没有得到想要的答案。出了什么问题，我该怎么办？

Answer 1

试试这个。我认为您需要一个 GROUP BY 并且我在 WHERE 中输入以过滤掉不是当前月份的行。

select pvs.contract_id,
       count(ir.contract_id) as counts,
       pvs.contract_date
from cbm_contract_period_value_split as pvs
left join cbm_contract_invoice_request as ir
on pvs.contract_id = ir.contract_id
and MONTH(ir.invoice_for_the_period_of_from_date) = MONTH(CURDATE())
WHERE MONTH(pvs.contract_date) = MONTH(CURDATE())
GROUP BY pvs.contract_id,pvs.contract_date

http://sqlfiddle.com/#!9/e5f08c/9

结果

contract_id     counts  contract_date
1               3       May, 01 2016 00:00:00
3               0       May, 01 2016 00:00:00