mysql - 导致双行的原因以及受代码约束时如何修复

Question

我正在“挖掘”一些 php 代码，试图遵守我的购物车的“最佳实践”，不重写核心代码。所以我对插入代码的方式和位置受到限制。这是执行的 SQL 的结果:

    SELECT SQL_CALC_FOUND_ROWS
    b.*, a.*
    FROM `product` a
    LEFT JOIN `product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
     INNER JOIN product_shop product_shop
ON (product_shop.id_product = a.id_product AND product_shop.id_shop = 1)  JOIN `category_lang` cl ON (cl.id_category = a.id_category_default) 
    WHERE 1  AND cl.name <> "Archives" AND a.id_product IN (
    SELECT p.id_product
    FROM `product` p
     LEFT 
    JOIN stock_available stock
    ON (stock.id_product = p.id_product AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
    WHERE p.id_product NOT IN (
        SELECT DISTINCT(id_product)
        FROM `product_attribute`
    )
    AND IFNULL(stock.quantity, 0) <= 0
)
    ORDER BY a.`id_product` DESC LIMIT 0,50

我负责的代码是:

JOIN `category_lang` cl ON (cl.id_category = a.id_category_default)

还有

 AND cl.name <> "Archives"

我对插入位置没有发言权。

我实际上得到了我正在寻找的结果......只两次。每条记录都会重复返回。

Answer 1

我们找到的解决方案是GROUP BY “重复”的列，这是由您拥有的联接引起的。

GROUP BY a.id_product
ORDER BY a.`id_product` DESC LIMIT 0,50