如何获取MySQL分组后符合条件列的具体去重值？

要查看每个符合条件的column1分组对应的column2和column3具体去重值，你可以先筛选出满足原查询条件的分组，再提取这些分组下的去重字段值并关联展示，具体实现如下：

方法1（MySQL 8.0+，支持CTE） #

WITH eligible_groups AS (
    -- 先筛选出符合条件的column1分组
    SELECT column1 
    FROM table1
    GROUP BY column1
    HAVING COUNT(DISTINCT column2) > 1 OR COUNT(DISTINCT column3) > 1
),
distinct_col2 AS (
    -- 获取目标分组下去重的column1和column2
    SELECT DISTINCT column1, column2 
    FROM table1 
    WHERE column1 IN (SELECT column1 FROM eligible_groups)
),
distinct_col3 AS (
    -- 获取目标分组下去重的column1和column3
    SELECT DISTINCT column1, column3 
    FROM table1 
    WHERE column1 IN (SELECT column1 FROM eligible_groups)
)
-- 关联两个去重结果，得到所有字段值组合
SELECT dc2.column1, dc2.column2, dc3.column3
FROM distinct_col2 dc2
JOIN distinct_col3 dc3 ON dc2.column1 = dc3.column1;

方法2（兼容MySQL 8.0以下版本） #

SELECT dc2.column1, dc2.column2, dc3.column3
FROM (
    SELECT DISTINCT column1, column2 
    FROM table1
    WHERE column1 IN (
        SELECT column1 
        FROM table1
        GROUP BY column1
        HAVING COUNT(DISTINCT column2) > 1 OR COUNT(DISTINCT column3) > 1
    )
) dc2
JOIN (
    SELECT DISTINCT column1, column3 
    FROM table1
    WHERE column1 IN (
        SELECT column1 
        FROM table1
        GROUP BY column1
        HAVING COUNT(DISTINCT column2) > 1 OR COUNT(DISTINCT column3) > 1
    )
) dc3 ON dc2.column1 = dc3.column1;

示例输出 #

以你提到的abcd分组为例（假设column2唯一值为val2，column3去重值为val3a、val3b），输出结果如下：

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