mysql - 来自两个连接表的计数显示相同，即使它们不应该

Question

我有一个非常直接的查询，它选择几个公司名称并计算其他两个连接表的结果。出于某种原因，我得到的优惠数量与规则相同，即使该数量仅适用于规则。

例如我有 2 个报价和 6 个规则。返回结果均显示为6。

我错过了什么？

SELECT
    business_profile.busID,
    business_profile.busName,
    COUNT(business_offers.ofr_id) AS cntOffers,
    COUNT(business_rules.rule_id) AS cntRules
FROM business_profile
LEFT JOIN business_offers ON (business_offers.ofr_busID = business_profile.busID)
LEFT JOIN business_rules ON (business_rules.rule_busID = business_profile.busID)
WHERE business_profile.busID > 1
GROUP BY business_profile.busID
ORDER BY cntRules DESC, cntOffers DESC 
LIMIT 20

Answer 1

使用 DISTINCT 这样您就不会多次计算同一个 ID。

SELECT
    business_profile.busID,
    business_profile.busName,
    COUNT(DISTINCT business_offers.ofr_id) AS cntOffers,
    COUNT(DISTINCT business_rules.rule_id) AS cntRules