如何筛选多表关联查询的重复行并删除、保留order值最低的记录

1 生成全量重复行报表 #

你原有的关联查询存在语法冗余（FROM子句中a后面多余的逗号），我们可以基于窗口函数实现全量p_id覆盖的重复行筛选，不需要硬编码p_id条件：

WITH base_query AS (
    -- 修正后的基础关联查询，覆盖所有p_id
    SELECT   
        a.name, a.`group`, a.`order`, c.p_id
    FROM a
    JOIN b USING (p_id)
    JOIN c USING (p_id)
),
ranked_data AS (
    -- 按name、group、p_id分组，按order升序排序打标
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY name, `group`, p_id ORDER BY `order` ASC) AS rn
    FROM base_query
)
-- 筛选所有重复行：同分组内order不是最小值的记录
SELECT name, `group`, `order`, p_id 
FROM ranked_data 
WHERE rn > 1;

注意：group和order属于SQL保留关键字，查询时需要用反引号包裹避免语法报错。
该查询返回的结果就是你需要的重复行报表，包含所有p_id下的冗余记录。

2 去重实现 #

2.1 生成去重后的查询结果 #

如果不需要修改原始表，只需要返回去重后的结果，直接筛选分组内排序为1的记录即可：

WITH base_query AS (
    SELECT   
        a.name, a.`group`, a.`order`, c.p_id
    FROM a
    JOIN b USING (p_id)
    JOIN c USING (p_id)
),
ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY name, `group`, p_id ORDER BY `order` ASC) AS rn
    FROM base_query
)
SELECT name, `group`, `order`, p_id 
FROM ranked_data 
WHERE rn = 1;

2.2 直接删除原始表冗余数据 #

如果需要永久删除a表中的冗余记录，操作前请先备份数据，可使用以下语句（以MySQL为例，其他数据库语法可对应调整）：

DELETE t1 FROM a t1
JOIN a t2 
  ON t1.name = t2.name 
  AND t1.`group` = t2.`group` 
  AND t1.p_id = t2.p_id 
  AND t1.`order` > t2.`order`;

该语句会自动删除每个name+group+p_id分组下，order值不是最小的所有冗余记录。

内容的提问来源于stack exchange，提问作者userr not found