如何用T-SQL删除user_id重复行仅保留首次出现的记录？

Hey there! Let's figure out how to fix this duplicate user_id problem, and more importantly, understand why certain approaches work (and why yours didn't). Your goal is to keep only the first occurrence of each user_id and delete the rest—let's break this down step by step.

First: Why Your Initial Attempt Failed #

Looking at your query:

DELETE FROM t WHERE user_id IN (SELECT user_id FROM t GROUP BY user_id HAVING COUNT(*)>1)

The issue here is that this deletes all rows for any user_id that has duplicates. For example, user_id 123 has two rows—your query sees it's a duplicate, so it deletes both rows instead of keeping the first one. You're targeting the entire user_id group, not just the duplicate entries within the group.

Core Solving思路 (and原理) #

The key idea is to label each row to identify which one is the "first occurrence" of its user_id, then keep only those labeled rows (or delete the others). Here's how this works:

1. Use Window Functions to Rank Rows #

SQL's window functions let you group rows (by user_id here) and assign a unique number to each row within the group. The ROW_NUMBER() function is perfect for this:

• PARTITION BY user_id: Splits your table into groups where each group has the same user_id.
• ORDER BY [some column]: Determines the order of rows within each group. To keep the first inserted row, use a column that tracks insertion order (like an auto-increment ID if you have one). If you don't have such a column, in SQL Server you can use ORDER BY (SELECT 0) to approximate the insertion order (note: this works best for heap tables; if you have a clustered index, use that index column instead for reliability).

When you run this, each user_id group will have rows numbered 1, 2, 3... where row 1 is the first occurrence you want to keep.

2. Filter or Delete Based on the Rank #

Once you have the ranks, you can either:

• Select only the rows with rank 1 to get your desired result, or
• Delete all rows with rank > 1 to remove duplicates.

Step-by-Step Solutions #

Solution 1: Get the Desired Result (Select Only First Occurrences) #

This query returns exactly the rows you want to keep:

SELECT user_id, PAios_AccountId
FROM (
    SELECT 
        user_id, 
        PAios_AccountId,
        -- Assign row numbers per user_id, ordered by insertion order
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY (SELECT 0)) AS row_rank
    FROM t
) AS ranked_rows
WHERE row_rank = 1;

Solution 2: Delete Duplicate Rows (Keep First Occurrences) #

If you want to modify the original table by deleting duplicates, use a CTE (Common Table Expression) to rank rows first, then delete the non-first ones:

WITH ranked_rows AS (
    SELECT 
        user_id, 
        PAios_AccountId,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY (SELECT 0)) AS row_rank
    FROM t
)
DELETE FROM ranked_rows WHERE row_rank > 1;

Key Notes for Reliability #

• If your table has an auto-increment primary key (like id INT IDENTITY(1,1)), replace ORDER BY (SELECT 0) with ORDER BY id ASC. This guarantees you keep the earliest inserted row, since the ID increments with each new row.
• The ROW_NUMBER() function is supported in most modern SQL databases (SQL Server, PostgreSQL, MySQL 8+, etc.), though syntax might vary slightly (e.g., MySQL uses ORDER BY NULL instead of (SELECT 0)).

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