如何筛选GROUP_CONCAT列存在重复值的MySQL查询结果

原查询语句：

SELECT
    cpe.entity_id AS configurable_product_id,
    cpe.sku AS configurable_product_sku,
    GROUP_CONCAT(DISTINCT cpsa.attribute_id ORDER BY cpsa.attribute_id SEPARATOR ',') AS configurable_super_attribute_ids,
    cp.entity_id AS simple_product_id,
    cp.sku AS simple_product_sku,
    GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values
FROM
    catalog_product_entity AS cpe
INNER JOIN catalog_product_super_link AS cpsl ON cpsl.parent_id = cpe.entity_id
INNER JOIN catalog_product_entity AS cp ON cp.entity_id = cpsl.product_id
INNER JOIN catalog_product_super_attribute AS cpsa ON cpsa.product_id = cpe.entity_id
INNER JOIN catalog_product_entity_int AS cpei ON cpei.attribute_id = cpsa.attribute_id AND cpei.`entity_id` = cp.`entity_id`
GROUP BY cpe.entity_id, cp.entity_id
LIMIT 100;

原查询结果示例：

configurable_product_id configurable_product_sku    configurable_super_attribute_ids    simple_product_id   simple_product_sku  simple_product_super_attribute_values
90086   Iphone  93,165  119 093 93_5730,165_7070
90086   Iphone  93,165  124 104 93_5730,165_7071
90086   Iphone  93,165  128 114 93_5707,165_7072
90086   Iphone  93,165  169 156 93_5727,165_7072
90086   Iphone  93,165  181 163 93_5730,165_7070
90086   Iphone  93,165  186 194 93_5727,165_7071
90086   Iphone  93,165  146 023 93_5730,165_7071

需要保留simple_product_super_attribute_values有重复的记录（即示例中的第1、2、5、7行）。

解决方案一：使用子查询统计重复次数 #

SELECT main.*
FROM (
    SELECT
        cpe.entity_id AS configurable_product_id,
        cpe.sku AS configurable_product_sku,
        GROUP_CONCAT(DISTINCT cpsa.attribute_id ORDER BY cpsa.attribute_id SEPARATOR ',') AS configurable_super_attribute_ids,
        cp.entity_id AS simple_product_id,
        cp.sku AS simple_product_sku,
        GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values
    FROM
        catalog_product_entity AS cpe
    INNER JOIN catalog_product_super_link AS cpsl ON cpsl.parent_id = cpe.entity_id
    INNER JOIN catalog_product_entity AS cp ON cp.entity_id = cpsl.product_id
    INNER JOIN catalog_product_super_attribute AS cpsa ON cpsa.product_id = cpe.entity_id
    INNER JOIN catalog_product_entity_int AS cpei ON cpei.attribute_id = cpsa.attribute_id AND cpei.`entity_id` = cp.`entity_id`
    GROUP BY cpe.entity_id, cp.entity_id
) AS main
INNER JOIN (
    SELECT
        cpe.entity_id AS configurable_product_id,
        GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values,
        COUNT(*) AS duplicate_count
    FROM
        catalog_product_entity AS cpe
    INNER JOIN catalog_product_super_link AS cpsl ON cpsl.parent_id = cpe.entity_id
    INNER JOIN catalog_product_entity AS cp ON cp.entity_id = cpsl.product_id
    INNER JOIN catalog_product_super_attribute AS cpsa ON cpsa.product_id = cpe.entity_id
    INNER JOIN catalog_product_entity_int AS cpei ON cpei.attribute_id = cpsa.attribute_id AND cpei.`entity_id` = cp.`entity_id`
    GROUP BY cpe.entity_id, simple_product_super_attribute_values
    HAVING duplicate_count > 1
) AS duplicates 
    ON main.configurable_product_id = duplicates.configurable_product_id
    AND main.simple_product_super_attribute_values = duplicates.simple_product_super_attribute_values
LIMIT 100;

解决方案二：使用CTE（MySQL 8.0+支持） #

如果你的MySQL版本是8.0及以上，用CTE结构更清晰：

WITH product_attrs AS (
    SELECT
        cpe.entity_id AS configurable_product_id,
        cpe.sku AS configurable_product_sku,
        GROUP_CONCAT(DISTINCT cpsa.attribute_id ORDER BY cpsa.attribute_id SEPARATOR ',') AS configurable_super_attribute_ids,
        cp.entity_id AS simple_product_id,
        cp.sku AS simple_product_sku,
        GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values
    FROM
        catalog_product_entity AS cpe
    INNER JOIN catalog_product_super_link AS cpsl ON cpsl.parent_id = cpe.entity_id
    INNER JOIN catalog_product_entity AS cp ON cp.entity_id = cpsl.product_id
    INNER JOIN catalog_product_super_attribute AS cpsa ON cpsa.product_id = cpe.entity_id
    INNER JOIN catalog_product_entity_int AS cpei ON cpei.attribute_id = cpsa.attribute_id AND cpei.`entity_id` = cp.`entity_id`
    GROUP BY cpe.entity_id, cp.entity_id
),
duplicate_attrs AS (
    SELECT
        configurable_product_id,
        simple_product_super_attribute_values,
        COUNT(*) AS duplicate_count
    FROM product_attrs
    GROUP BY configurable_product_id, simple_product_super_attribute_values
    HAVING duplicate_count > 1
)
SELECT pa.*
FROM product_attrs pa
INNER JOIN duplicate_attrs da
    ON pa.configurable_product_id = da.configurable_product_id
    AND pa.simple_product_super_attribute_values = da.simple_product_super_attribute_values
LIMIT 100;

逻辑说明 #

1. 先通过主查询生成所有产品属性组合的完整记录。
2. 再通过统计子查询/CTE，计算同一可配置产品下每个simple_product_super_attribute_values的出现次数，筛选出次数大于1的重复组合。
3. 最后将两个结果关联，只保留属于重复组合的原始记录，即可得到目标行。

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