MySQL查询重复数据：排除被引用最频繁的记录

问题背景 #

我在MySQL中有两张关联表，结构如下：

marker表 #

seat_marker表 #

查询需求 #

需要查询marker表中所有重复行，但排除在seat_marker表中被引用次数最多的行。即列出所有重复行，排除每组重复数据中在seat_marker里出现次数最多的「原始」行。

预期结果 #

结果说明 #

原始marker表移除了三行：

• id 1被移除：它是foo-bar-baz重复组中在seat_marker中引用次数最多的行；
• id 4被移除：它属于qux-bar-baz重复组，该组在seat_marker中引用次数为0，移除id4或5均可，此处随机选择移除id4；
• id6被移除：它没有重复行，不属于重复组。

当前SQL（存在问题） #

我目前写出的SQL能列出所有重复值及其在seat_marker中的引用次数，但无法排除「原始」行：

-- 选取所有重复行，并列出其在seat_marker表中的引用次数
SELECT 
  n1.id, n1.SEASONCD, n1.ITEMCD, n1.PRICETYPECD, (
    SELECT COUNT(1) 
    FROM seat_marker 
    WHERE MARKER_ID = n1.id
  ) active_assignments
FROM marker n1, marker n2 
WHERE
    n1.id <> n2.id AND 
    n1.SEASONCD = n2.SEASONCD AND 
    n1.ITEMCD = n2.ITEMCD AND
    n1.PRICETYPECD = n2.PRICETYPECD
GROUP BY id, SEASONCD, ITEMCD, PRICETYPECD, active_assignments
ORDER BY SEASONCD, ITEMCD, PRICETYPECD, active_assignments DESC, id;

解决方案 #

要实现需求，需先确定每组重复数据中需保留的「原始」行（引用次数最多，次数相同时取最小id），再筛选出其余重复行。以下是可行的SQL语句：

WITH marker_with_counts AS (
    -- 计算每个marker在seat_marker中的引用次数
    SELECT 
        m.id,
        m.SEASONCD,
        m.ITEMCD,
        m.PRICETYPECD,
        COALESCE(COUNT(sm.MARKER_ID), 0) AS reference_count
    FROM marker m
    LEFT JOIN seat_marker sm ON m.id = sm.MARKER_ID
    GROUP BY m.id, m.SEASONCD, m.ITEMCD, m.PRICETYPECD
),
group_top_markers AS (
    -- 找出每个重复组中需保留的原始行：引用次数最高，次数相同则取最小id
    SELECT 
        SEASONCD,
        ITEMCD,
        PRICETYPECD,
        FIRST_VALUE(id) OVER (
            PARTITION BY SEASONCD, ITEMCD, PRICETYPECD 
            ORDER BY reference_count DESC, id ASC
        ) AS keep_id
    FROM marker_with_counts
    -- 仅处理存在重复的组
    WHERE EXISTS (
        SELECT 1 
        FROM marker m2 
        WHERE m2.SEASONCD = marker_with_counts.SEASONCD
          AND m2.ITEMCD = marker_with_counts.ITEMCD
          AND m2.PRICETYPECD = marker_with_counts.PRICETYPECD
          AND m2.id <> marker_with_counts.id
    )
)
-- 筛选出重复组中除原始行外的所有行
SELECT m.id, m.SEASONCD, m.ITEMCD, m.PRICETYPECD
FROM marker m
JOIN group_top_markers gtm 
    ON m.SEASONCD = gtm.SEASONCD
    AND m.ITEMCD = gtm.ITEMCD
    AND m.PRICETYPECD = gtm.PRICETYPECD
    AND m.id <> gtm.keep_id
ORDER BY m.id;

语句说明 #

1. marker_with_counts CTE：通过左连接计算每个marker的引用次数，用COALESCE确保无引用时次数为0；
2. group_top_markers CTE：针对每个重复组，用窗口函数FIRST_VALUE选出引用次数最高的行，次数相同时取id最小的作为保留行；
3. 最终查询：关联两张表，排除每个组的保留行，得到目标重复行结果。

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