mysql - 如何根据不同的id从同一个表中获取公共(public)记录？

Question

我的数据库中有一个 mysql 表，如下所示

 company_id | bill_month |
 134        |Apr
 134        |May
 134        |Jun
 134        |Jul
 134        |Aug2k19
 134        |Sep2k19
 391        |Aug2k19
 391        |Sep2k19

现在我想要的是获取所有公司共有的月份，我想要的结果如下所示

 bill_months|
 Aug2k19    |
 Sep2k19    |

我尝试使用自连接和以下查询

 SELECT DISTINCT a.bill_month FROM bf_monthly_bill AS a INNER JOIN bf_monthly_bill AS b 
 on a.bill_month=b.bill_month WHERE a.company_id IN('134','391')

Answer 1

您可以按 bill_month 分组并在 HAVING 子句中设置条件:

SELECT bill_month 
FROM bf_monthly_bill 
WHERE company_id IN ('134','391')
GROUP BY bill_month
HAVING COUNT(DISTINCT company_id) = 2