如何编写SQL（SQLite3/PostgreSQL/MySQL）基于表B合并去重表A？

Hey there! Let's break down how to deduplicate your contacts table using data from the phones table. The core idea here is: if two contacts share any phone number, they're the same person—even if fields like company don't match (like your example where two Charles are duplicates, but two Bettys aren't).

First, let's set up sample table structures to work with (adjust these to match your actual schema):

-- Contacts table: stores core contact info
CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    name VARCHAR(50),
    company VARCHAR(100),
    create_time DATETIME -- Optional: helps pick which record to keep as the "main" one
);

-- Phones table: links contacts to their phone numbers (one contact can have multiple numbers)
CREATE TABLE phones (
    phone_id INT PRIMARY KEY,
    contact_id INT REFERENCES contacts(contact_id),
    phone_number VARCHAR(20) -- Remove UNIQUE if the same number can be linked multiple times
);

Scenario 1: Flag Duplicate Contacts (Keep All Records) #

If you want to first audit duplicates without modifying data, this query will label which records are duplicates and which are the main (keep) record.

PostgreSQL & MySQL #

We'll use window functions to group contacts by shared phone numbers, then flag non-main records:

WITH contact_phone_groups AS (
    -- Group contacts by their phone numbers, rank records (newest first as main)
    SELECT 
        p.phone_number,
        c.contact_id,
        c.name,
        c.company,
        ROW_NUMBER() OVER (
            PARTITION BY p.phone_number 
            ORDER BY c.create_time DESC -- Change to ASC to keep oldest, or contact_id to keep smallest ID
        ) AS record_rank
    FROM phones p
    JOIN contacts c ON p.contact_id = c.contact_id
),
duplicate_ids AS (
    -- Get all contact IDs that are duplicates (not the first in their phone group)
    SELECT DISTINCT contact_id
    FROM contact_phone_groups
    WHERE record_rank > 1
)
SELECT 
    c.*,
    CASE 
        WHEN dc.contact_id IS NOT NULL THEN 'Duplicate' 
        ELSE 'Main Record' 
    END AS status
FROM contacts c
LEFT JOIN duplicate_ids dc ON c.contact_id = dc.contact_id;

SQLite3 #

SQLite3 supports window functions starting from version 3.25.0, so the above query works directly—just ensure your create_time is stored in a sortable format (like YYYY-MM-DD HH:MM:SS):

WITH contact_phone_groups AS (
    SELECT 
        p.phone_number,
        c.contact_id,
        c.name,
        c.company,
        ROW_NUMBER() OVER (
            PARTITION BY p.phone_number 
            ORDER BY c.create_time DESC
        ) AS record_rank
    FROM phones p
    JOIN contacts c ON p.contact_id = c.contact_id
),
duplicate_ids AS (
    SELECT DISTINCT contact_id
    FROM contact_phone_groups
    WHERE record_rank > 1
)
SELECT 
    c.*,
    CASE 
        WHEN dc.contact_id IS NOT NULL THEN 'Duplicate' 
        ELSE 'Main Record' 
    END AS status
FROM contacts c
LEFT JOIN duplicate_ids dc ON c.contact_id = dc.contact_id;

Scenario 2: Create a Deduplicated Contacts Table (Merge Duplicates) #

If you want to generate a clean, deduplicated table where duplicate contacts are merged—we'll aggregate fields like company (since they might differ) and keep one main record per unique contact.

PostgreSQL #

Use STRING_AGG() to combine company names, and group by a shared "group ID" for duplicate contacts:

WITH contact_groups AS (
    -- Assign a group ID to each contact based on shared phone numbers (use newest contact as group leader)
    SELECT 
        c.contact_id,
        FIRST_VALUE(c.contact_id) OVER (
            PARTITION BY p.phone_number 
            ORDER BY c.create_time DESC
        ) AS group_id
    FROM phones p
    JOIN contacts c ON p.contact_id = c.contact_id
),
unique_groups AS (
    -- Ensure each contact is linked to only one group ID
    SELECT 
        contact_id,
        MAX(group_id) AS final_group_id -- Use MIN if you prefer the oldest contact as leader
    FROM contact_groups
    GROUP BY contact_id
)
SELECT 
    ug.final_group_id AS new_contact_id,
    MAX(c.name) AS name, -- Assume names are same; use STRING_AGG if they might differ
    STRING_AGG(DISTINCT c.company, ', ') AS combined_companies,
    MAX(c.create_time) AS latest_update
FROM unique_groups ug
JOIN contacts c ON ug.contact_id = c.contact_id
GROUP BY ug.final_group_id
ORDER BY ug.final_group_id;

MySQL #

Replace STRING_AGG() with MySQL's GROUP_CONCAT()—otherwise the logic is identical:

WITH contact_groups AS (
    SELECT 
        c.contact_id,
        FIRST_VALUE(c.contact_id) OVER (
            PARTITION BY p.phone_number 
            ORDER BY c.create_time DESC
        ) AS group_id
    FROM phones p
    JOIN contacts c ON p.contact_id = c.contact_id
),
unique_groups AS (
    SELECT 
        contact_id,
        MAX(group_id) AS final_group_id
    FROM contact_groups
    GROUP BY contact_id
)
SELECT 
    ug.final_group_id AS new_contact_id,
    MAX(c.name) AS name,
    GROUP_CONCAT(DISTINCT c.company SEPARATOR ', ') AS combined_companies,
    MAX(c.create_time) AS latest_update
FROM unique_groups ug
JOIN contacts c ON ug.contact_id = c.contact_id
GROUP BY ug.final_group_id
ORDER BY ug.final_group_id;

SQLite3 #

SQLite3 uses GROUP_CONCAT() for string aggregation, and supports the window function logic:

WITH contact_groups AS (
    SELECT 
        c.contact_id,
        FIRST_VALUE(c.contact_id) OVER (
            PARTITION BY p.phone_number 
            ORDER BY c.create_time DESC
        ) AS group_id
    FROM phones p
    JOIN contacts c ON p.contact_id = c.contact_id
),
unique_groups AS (
    SELECT 
        contact_id,
        MAX(group_id) AS final_group_id
    FROM contact_groups
    GROUP BY contact_id
)
SELECT 
    ug.final_group_id AS new_contact_id,
    MAX(c.name) AS name,
    GROUP_CONCAT(DISTINCT c.company, ', ') AS combined_companies,
    MAX(c.create_time) AS latest_update
FROM unique_groups ug
JOIN contacts c ON ug.contact_id = c.contact_id
GROUP BY ug.final_group_id
ORDER BY ug.final_group_id;

Key Notes to Remember #

• Duplicate Rule Adjustment: The above logic uses "any shared phone number = same contact". If you need to only flag duplicates where all phone numbers match, you'll need to aggregate phone numbers per contact first, then group by that aggregated list.
• Field Handling: For fields like name that might differ between duplicates, adjust the aggregation (use STRING_AGG/GROUP_CONCAT instead of MAX).
• Backup First: Always back up your original data before running any merge/delete operations—better safe than sorry!

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