mysql - LEFT JOIN 创建多行、GROUP BY 或 DISTINCT

Question

因此，我尝试 LEFT JOIN 一个表来检查商店上是否有正在运行的事件，但如果存在匹配，行似乎会重复。

我尝试使用 DISTINCT 和 GROUP BY，但它的行为并不像我想要的那样。

SELECT
    s.id AS ShopID,
    s.name,
    si.id AS shop_item_id,
    si.item_price,
    p.cat_id AS category_id,
    c.campaign_desc,
    c.campaign_type_id,
    c.shop_id AS campaign_shop_id

FROM
    shop_item si 

    JOIN
        shop s ON 
        s.id = si.shop_id
    JOIN
         product p ON 
         si.product_id = p.id
    LEFT JOIN 
        campaign_category cc ON 
        cc.category_id = p.cat_id
    LEFT JOIN 
        campaign c ON 
        c.id = cc.campaign_id AND
        c.shop_id = si.shop_id
WHERE
    si.`product_id` = 299 AND
    s.`active_shop` = 1

ORDER BY
    si.`item_price`,
    ShopID,
    c.campaign_desc DESC

但是如果我添加

GROUP BY ShopID 

它返回这个

第一家商店“Shop 1”突然缺少“黑色星期五”。我也尝试过 DISTINCT 但没有任何运气，我现在有点困惑。我错过了什么？

致以诚挚的问候约翰

Answer 1

使用带有 max 聚合的 group by 子句，因为重复的列值会为描述、campeign_id 和 shop_id 生成空值，这些可以使用 max 来处理，但是如果您共享表结构，那么很容易确定它产生的原因重复记录以及最佳解决方案是什么

SELECT
    s.id AS ShopID,
    s.name,
    si.id AS shop_item_id,
    si.item_price,
    p.cat_id AS category_id,
    max(c.campaign_desc) as desc,
    max(c.campaign_type_id) as campaign_type_id,
    max(c.shop_id) AS campaign_shop_id

FROM
    shop_item si 

    JOIN
        shop s ON 
        s.id = si.shop_id
    JOIN
         product p ON 
         si.product_id = p.id
    LEFT JOIN 
        campaign_category cc ON 
        cc.category_id = p.cat_id
    LEFT JOIN 
        campaign c ON 
        c.id = cc.campaign_id AND
        c.shop_id = si.shop_id
WHERE
    si.`product_id` = 299 AND
    s.`active_shop` = 1
group by s.id,
    s.name,
    si.id ,
    si.item_price
ORDER BY
    si.`item_price`,
    ShopID,
    c.campaign_desc DESC