多表连接为何导致数据重复？PostgreSQL查询案例解析

我们有四张表：Product、SpecialOffers、SpecialOfferProducts和ProductPurchases，表结构如下：

Product表 #

SpecialOffers表 #

SpecialOfferProducts表（记录参与优惠的产品） #

ProductPurchases表（记录产品采购数量及关联优惠） #

业务场景 #

产品可参与0个、1个或多个特价优惠：

• Offer 1包含Product A、B、C
• Offer 2包含Product A、B
• Offer 3仅包含Product D
• Product E未参与任何优惠

产品采购数据记录在ProductPurchases表中。

尝试的查询语句 #

我写了PostgreSQL查询，想返回产品所有字段，加上两个JSON列（分别存该产品的优惠信息、采购信息）：

SELECT P.*, 
       JSON_AGG(JSON_BUILD_OBJECT('offer.id', SO.ID, 'offer.name', SO.NAME)) AS OFFERS, 
       JSON_AGG(JSON_BUILD_OBJECT('purchase.id', PP.ID, 'purchase.specialOfferId', PP.SPECIAL_OFFER, 'purchase.specialOfferName', SO.NAME)) AS PURCHASES
FROM PRODUCTS P
LEFT JOIN SPECIAL_OFFER_PRODUCTS SOP ON SOP.PRODUCT_ID = P.ID
LEFT JOIN SPECIAL_OFFERS SO ON SO.ID = SOP.SPECIAL_OFFER_ID
LEFT JOIN PRODUCT_PURCHASE PP ON PP.PRODUCT_ID = P.ID
GROUP BY P.ID

遇到的问题 #

结果出现数据重复：

• Product A的优惠和采购数被错误统计为6条（实际应为3条采购、2条优惠）
• Product B的统计数为4条（实际应为2条采购、2条优惠）
• 其余产品数据正常

统计数是实际数的倍数（比如新增1条采购，统计数会变为8），请问为什么会出现这种情况？

这是多表连接导致的笛卡尔积问题。当你同时左连接SpecialOfferProducts（关联优惠）和ProductPurchases（关联采购）时，每个优惠记录会和每个采购记录进行交叉匹配：

• 以Product A为例：它有2条优惠记录（Offer1、Offer2），3条采购记录，交叉后产生2×3=6条组合记录，所以JSON_AGG会把这6条都聚合进去，导致重复。
• Product B有2条优惠、2条采购，交叉后是2×2=4条组合记录，所以统计数是4。

要避免笛卡尔积，需要分别对优惠和采购进行子查询聚合，再将结果与主表Product连接：

SELECT 
    p.*,
    COALESCE(o.offers, '[]'::json) AS offers,
    COALESCE(pur.purchases, '[]'::json) AS purchases
FROM products p
LEFT JOIN (
    SELECT 
        sop.product_id,
        JSON_AGG(JSON_BUILD_OBJECT('offer.id', so.id, 'offer.name', so.name)) AS offers
    FROM special_offer_products sop
    JOIN special_offers so ON so.id = sop.special_offer_id
    GROUP BY sop.product_id
) o ON o.product_id = p.id
LEFT JOIN (
    SELECT 
        pp.product_id,
        JSON_AGG(JSON_BUILD_OBJECT(
            'purchase.id', pp.id, 
            'purchase.specialOfferId', pp.special_offer_id, 
            'purchase.specialOfferName', so.name
        )) AS purchases
    FROM product_purchases pp
    LEFT JOIN special_offers so ON so.id = pp.special_offer_id
    GROUP BY pp.product_id
) pur ON pur.product_id = p.id;

说明 #

1. 第一个子查询o：单独聚合每个产品的优惠信息，避免和采购数据交叉。
2. 第二个子查询pur：单独聚合每个产品的采购信息，同时关联优惠名称（如果有的话）。
3. 使用COALESCE处理没有优惠或采购的产品，返回空JSON数组[]，而不是null。

这样就能得到准确的统计结果：每个产品的优惠和采购数都是实际数量，不会出现重复。

内容的提问来源于stack exchange，提问作者nighthawk