SQL Server合并重复用户行：保留晚注册记录并补全空值

Alright, let's work through this duplicate user record cleanup step by step—this is a super common task when maintaining user databases, and I’ve helped folks sort this out plenty of times. Here’s how you can keep the latest (high-priority) record while merging in all the non-empty details from older, incomplete entries:

First, let’s make sure we’re on the same page: you want to keep the user record with the latest registration date for each email, but fill in any blank fields on that record with the non-empty values from older records tied to the same email. Perfect, let’s break this down into safe, testable steps.

Step 1: Identify which records to keep and which to merge #

First, let’s label each record in duplicate email groups so we know which is the "main" one (latest registered) and which are the "duplicates" to merge from. We’ll use a window function to rank records per email by registration date:

SELECT 
    id,
    email,
    registered_at,
    gender,
    address,
    phone,
    -- Assign rank 1 to the latest record for each email
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY registered_at DESC) AS record_rank
FROM Users;

Run this query first—you’ll see record_rank = 1 for the high-priority records we want to keep, and record_rank > 1 for the older ones with extra details.

Step 2: Merge non-empty fields into the main record #

Next, we’ll update the main records to fill in their blank fields with values from older duplicates. We’ll use COALESCE() here because it returns the first non-null value it finds—exactly what we need (if the main record’s field is empty, grab the value from an older one).

Example for MySQL:

UPDATE Users main_record
JOIN (
    -- First, rank all records per email
    SELECT 
        email,
        MAX(registered_at) AS latest_reg_date,
        -- Use COALESCE to prioritize main record's value, then older ones
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN gender END), MAX(CASE WHEN record_rank > 1 THEN gender END)) AS merged_gender,
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN address END), MAX(CASE WHEN record_rank > 1 THEN address END)) AS merged_address,
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN phone END), MAX(CASE WHEN record_rank > 1 THEN phone END)) AS merged_phone
    FROM (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY registered_at DESC) AS record_rank
        FROM Users
    ) ranked_records
    GROUP BY email
) merged_data ON main_record.email = merged_data.email AND main_record.registered_at = merged_data.latest_reg_date
SET 
    main_record.gender = merged_data.merged_gender,
    main_record.address = merged_data.merged_address,
    main_record.phone = merged_data.merged_phone;

Example for PostgreSQL:

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY registered_at DESC) AS record_rank,
        MAX(registered_at) OVER (PARTITION BY email) AS latest_reg_date
    FROM Users
),
merged_fields AS (
    SELECT 
        email,
        latest_reg_date,
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN gender END), MAX(CASE WHEN record_rank > 1 THEN gender END)) AS gender,
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN address END), MAX(CASE WHEN record_rank > 1 THEN address END)) AS address,
        COALESCE(MAX(CASE WHEN record_rank = 1 THEN phone END), MAX(CASE WHEN record_rank > 1 THEN phone END)) AS phone
    FROM ranked_records
    GROUP BY email, latest_reg_date
)
UPDATE Users u
SET 
    gender = mf.gender,
    address = mf.address,
    phone = mf.phone
FROM merged_fields mf
WHERE u.email = mf.email AND u.registered_at = mf.latest_reg_date;

Before running the update, replace gender, address, phone with all the fields you need to merge. Also, run the inner subqueries separately to verify the merged values look correct!

Step 3: Delete the old duplicate records #

Once you’ve confirmed the main records have all the merged details, you can safely delete the older duplicates. Again, test this first with a SELECT to make sure you’re not deleting the wrong records:

Safe test query first:

SELECT *
FROM Users
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY registered_at DESC) AS record_rank
        FROM Users
    ) ranked
    WHERE record_rank = 1
);

If this shows only the old duplicates you want to remove, run the delete:

DELETE FROM Users
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY email ORDER BY registered_at DESC) AS record_rank
        FROM Users
    ) ranked
    WHERE record_rank = 1
);

Critical Notes! #

• Backup your data first: Always take a full backup of the Users table before running UPDATE or DELETE queries—mistakes happen, and you don’t want to lose data.
• Test each step: Run SELECT versions of the queries first to confirm the results match what you expect before making changes.
• Adjust for your schema: If your table has more fields to merge, just add more COALESCE() lines for each field in the merge step.

内容的提问来源于stack exchange，提问作者Agnes Wilk G