Oracle：基于关联表按分组删除重复记录

Got it, let's tackle this problem step by step. You need to deduplicate the rc_document table such that each document_id only stays in the group with the highest priority from rc_document_group, and delete all other duplicate entries for the same document_id.

First, let's break down the core logic:

• For each document_id, calculate the highest priority value across all groups it belongs to.
• Identify which specific rc_document records are part of that highest-priority group (these are the ones we want to keep).
• Delete all records in rc_document that aren't in this "keep list".

Step 1: Calculate highest priority per document_id #

First, let's get the maximum priority each document is associated with, using a join between the two tables:

SELECT 
    d.document_id,
    MAX(g.priority) AS highest_priority
FROM rc_document d
JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
GROUP BY d.document_id

Step 2: Identify records to retain #

Next, we use the above subquery to map back to the actual rc_document_id entries that need to be kept:

SELECT d.rc_document_id
FROM rc_document d
JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
JOIN (
    SELECT 
        d.document_id,
        MAX(g.priority) AS highest_priority
    FROM rc_document d
    JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
    GROUP BY d.document_id
) AS doc_highest 
    ON d.document_id = doc_highest.document_id 
    AND g.priority = doc_highest.highest_priority

Step 3: Delete unwanted records #

Finally, delete all records not in the keep list. This generic syntax works for most databases:

DELETE FROM rc_document
WHERE rc_document_id NOT IN (
    SELECT d.rc_document_id
    FROM rc_document d
    JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
    JOIN (
        SELECT 
            d.document_id,
            MAX(g.priority) AS highest_priority
        FROM rc_document d
        JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
        GROUP BY d.document_id
    ) AS doc_highest 
        ON d.document_id = doc_highest.document_id 
        AND g.priority = doc_highest.highest_priority
)

If you're using MySQL, a JOIN-based delete is often more efficient (and avoids potential edge cases with subqueries in NOT IN):

DELETE d_del
FROM rc_document d_del
LEFT JOIN (
    SELECT d.rc_document_id
    FROM rc_document d
    JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
    JOIN (
        SELECT 
            d.document_id,
            MAX(g.priority) AS highest_priority
        FROM rc_document d
        JOIN rc_document_group g ON d.rc_document_group_id = g.rc_document_group_id
        GROUP BY d.document_id
    ) AS doc_highest 
        ON d.document_id = doc_highest.document_id 
        AND g.priority = doc_highest.highest_priority
) d_keep ON d_del.rc_document_id = d_keep.rc_document_id
WHERE d_keep.rc_document_id IS NULL

Verification Against Sample Data #

Let's confirm this works with your input:

• document_id=1 has highest priority 2 (group 2), so we keep rc_document_id=5 and delete rc_document_id=1.
• document_id=3 has highest priority 2 (group 2), so we keep rc_document_id=6 and delete rc_document_id=3.
• document_id=2 and 4 only exist in group 1 (priority 1), so they're retained.

This exactly matches your expected output!

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