含DISTINCT的SQL查询为何返回重复id_product？求解决方案

嘿，我明白你遇到的困扰了！你用了DISTINCT但还是出现重复的id_product，核心原因很简单：DISTINCT是对查询返回的整行所有字段进行去重，而你的查询关联了ps_feature_product表——同一个产品（比如id=11941）对应了多个不同的id_feature_value（36和38），这就导致这两条记录的整体字段不完全一致，DISTINCT自然会把它们当成不同的结果保留下来。

下面给你几个实用的解决方案，你可以根据业务需求灵活选择：

方案1：用GROUP BY聚合多值字段 #

如果需要保留该产品的所有feature信息，可以用GROUP_CONCAT把同一个产品的多个feature值合并成逗号分隔的字符串，确保每个id_product只返回一行：

SELECT 
    p.`id_product`, 
    p.*, 
    product_shop.*, 
    pl.*, 
    m.`name` AS manufacturer_name, 
    GROUP_CONCAT(DISTINCT x.`id_feature` SEPARATOR ', ') AS id_features,
    GROUP_CONCAT(DISTINCT x.`id_feature_value` SEPARATOR ', ') AS id_feature_values,
    s.`name` AS supplier_name 
FROM `ps_product` p 
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) 
LEFT JOIN `ps_product_attribute` y ON (y.`id_product` = p.id_product) 
LEFT JOIN `ps_product_attribute_combination` ac ON (y.`id_product_attribute` = ac.`id_product_attribute`) 
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.id_shop = 1 ) 
LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.id_manufacturer) 
LEFT JOIN `ps_feature_product` x ON (x.`id_product` = p.id_product) 
LEFT JOIN `ps_supplier` s ON (s.`id_supplier` = p.id_supplier) 
LEFT JOIN `ps_category_product` c ON (c.`id_product` = p.id_product) 
WHERE pl.`id_lang` = 1 
  AND c.`id_category` = 18 
  AND p.`price` BETWEEN 0 AND 1000 
  AND product_shop.`visibility` IN ("both", "catalog") 
  AND product_shop.`active` = 1 
GROUP BY p.`id_product`
ORDER BY p.`id_product` ASC 
LIMIT 1,4

小提示：如果你的MySQL开启了ONLY_FULL_GROUP_BY模式（默认开启），需要确保SELECT中的非聚合字段都在GROUP BY里，或者对其他可能存在多值的字段也使用聚合函数（比如MAX()、MIN()）。

方案2：用窗口函数筛选唯一产品行 #

如果你只需要每个产品保留一条记录（不管对应哪个feature值），可以用ROW_NUMBER()窗口函数给每个id_product的行编号，然后只取编号为1的行：

WITH product_rows AS (
    SELECT 
        p.`id_product`, 
        p.*, 
        product_shop.*, 
        pl.*, 
        m.`name` AS manufacturer_name, 
        x.`id_feature`, 
        x.`id_feature_value`, 
        s.`name` AS supplier_name,
        -- 按id_feature_value排序，给每个产品的行编号
        ROW_NUMBER() OVER (PARTITION BY p.id_product ORDER BY x.id_feature_value) AS row_num
    FROM `ps_product` p 
    INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) 
    LEFT JOIN `ps_product_attribute` y ON (y.`id_product` = p.id_product) 
    LEFT JOIN `ps_product_attribute_combination` ac ON (y.`id_product_attribute` = ac.`id_product_attribute`) 
    LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.id_shop = 1 ) 
    LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.id_manufacturer) 
    LEFT JOIN `ps_feature_product` x ON (x.`id_product` = p.id_product) 
    LEFT JOIN `ps_supplier` s ON (s.`id_supplier` = p.id_supplier) 
    LEFT JOIN `ps_category_product` c ON (c.`id_product` = p.id_product) 
    WHERE pl.`id_lang` = 1 
      AND c.`id_category` = 18 
      AND p.`price` BETWEEN 0 AND 1000 
      AND product_shop.`visibility` IN ("both", "catalog") 
      AND product_shop.`active` = 1 
)
SELECT *
FROM product_rows
WHERE row_num = 1
ORDER BY id_product ASC
LIMIT 1,4

你可以修改ORDER BY x.id_feature_value来决定保留哪个feature的记录（比如换成ORDER BY x.id_feature DESC取最大的feature值）。

方案3：子查询先获取唯一产品列表 #

先通过子查询筛选出符合条件的唯一id_product，再关联其他表获取详细信息，这种方法能彻底避免关联多值表导致的重复：

SELECT 
    p.`id_product`, 
    p.*, 
    product_shop.*, 
    pl.*, 
    m.`name` AS manufacturer_name, 
    x.`id_feature`, 
    x.`id_feature_value`, 
    s.`name` AS supplier_name 
FROM (
    -- 先拿到4个唯一的产品ID
    SELECT DISTINCT p.id_product
    FROM `ps_product` p 
    INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) 
    LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.id_shop = 1 ) 
    LEFT JOIN `ps_category_product` c ON (c.`id_product` = p.id_product) 
    WHERE pl.`id_lang` = 1 
      AND c.`id_category` = 18 
      AND p.`price` BETWEEN 0 AND 1000 
      AND product_shop.`visibility` IN ("both", "catalog") 
      AND product_shop.`active` = 1 
    ORDER BY p.id_product ASC 
    LIMIT 1,4
) AS unique_products
INNER JOIN `ps_product` p ON p.id_product = unique_products.id_product
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) 
LEFT JOIN `ps_product_attribute` y ON (y.`id_product` = p.id_product) 
LEFT JOIN `ps_product_attribute_combination` ac ON (y.`id_product_attribute` = ac.`id_product_attribute`) 
LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.id_shop = 1 ) 
LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.id_manufacturer) 
LEFT JOIN `ps_feature_product` x ON (x.`id_product` = p.id_product) 
LEFT JOIN `ps_supplier` s ON (s.`id_supplier` = p.id_supplier) 
LEFT JOIN `ps_category_product` c ON (c.`id_product` = p.id_product) 
WHERE pl.`id_lang` = 1 
  AND c.`id_category` = 18 
  AND product_shop.`visibility` IN ("both", "catalog") 
  AND product_shop.`active` = 1 
ORDER BY p.`id_product` ASC

如果需要每个产品只显示一行，同样可以结合GROUP_CONCAT或者窗口函数处理关联后的feature数据。

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