LEFT JOIN多表统计值重复问题：合并单表统计至同一查询的解决方案

API 探路者

2026-8-9

问题原因解释

当你直接对Tb_Product做两次LEFT JOIN关联Tb_Offers和Tb_Requests时，会产生笛卡尔积效应：假设某产品对应3条Tb_Offers记录和2条Tb_Requests记录，关联后会生成3×2=6条重复组合的临时记录。此时用count(Tb_Offers.Prod_ID)统计时，每条临时记录都包含同一个Prod_ID，最终计数会变成6而非实际的3；同理Tb_Requests的计数也会变成6而非2，导致两个计数结果重复且错误。

解决方案

方案1：子查询预统计（推荐，性能更优）

先分别对Tb_Offers和Tb_Requests按Prod_ID分组统计数量，再将统计结果与Tb_Product关联，从根源避免笛卡尔积：

SELECT 
    p.Name,
    COALESCE(o.offer_count, 0) AS 'Number of Offers',
    COALESCE(r.request_count, 0) AS 'Number of Requests'
FROM Tb_Product p
LEFT JOIN (
    SELECT Prod_ID, COUNT(*) AS offer_count
    FROM Tb_Offers
    GROUP BY Prod_ID
) o ON p.Prod_ID = o.Prod_ID
LEFT JOIN (
    SELECT Prod_ID, COUNT(*) AS request_count
    FROM Tb_Requests
    GROUP BY Prod_ID
) r ON p.Prod_ID = r.Prod_ID
GROUP BY p.Name, o.offer_count, r.request_count;

用COALESCE是为了处理无对应Offers或Requests的产品，将NULL结果转为0，保证统计结果的完整性。

方案2：使用COUNT(DISTINCT)

如果Tb_Offers和Tb_Requests有唯一主键（比如Offer_ID、Request_ID），可以用COUNT(DISTINCT)抵消笛卡尔积带来的重复计数：

SELECT 
    Tb_Product.Name,
    COUNT(DISTINCT Tb_Offers.Offer_ID) AS 'Number of Offers',
    COUNT(DISTINCT Tb_Requests.Request_ID) AS 'Number of Requests'
FROM Tb_Product
LEFT OUTER JOIN Tb_Requests ON Tb_Product.Prod_ID = Tb_Requests.Prod_ID
LEFT OUTER JOIN Tb_Offers ON Tb_Product.Prod_ID = Tb_Offers.Prod_ID
GROUP BY Tb_Product.Name;