如何在SQL中基于指定字段去重并保留指定行（仅允许检索数据）

Got it, let's tackle this problem you've spent all day on! First, let's recap your scenario to make sure I'm on the same page:

You have a table with duplicate rows for the same ID (like ID 1 has two entries: one with Pregnancy = Yes, another with No). You need to retrieve data such that each ID only appears once, and we keep the row where Pregnancy is Yes if it exists. Plus, you can't use DELETE—only SELECT queries are allowed.

Method 1: Using Window Functions (Clean & Readable) #

This approach uses ROW_NUMBER() to assign a priority to each row within the same ID group, then we pick the highest priority row.

WITH ranked_records AS (
    SELECT
        ID,
        Name,
        Pregnancy,
        Gender,
        -- Assign row number: Yes gets priority (1), others get 2
        ROW_NUMBER() OVER (
            PARTITION BY ID
            ORDER BY CASE WHEN Pregnancy = 'Yes' THEN 1 ELSE 2 END
        ) AS row_rank
    FROM your_table_name -- Replace with your actual table name
)
SELECT ID, Name, Pregnancy, Gender
FROM ranked_records
WHERE row_rank = 1;

How this works:

• PARTITION BY ID: Groups all rows by their ID, so we handle each ID's duplicates separately.
• ORDER BY CASE...: Ensures that any row with Pregnancy = Yes gets the lowest row number (1), which means it's the first one we'll keep. For IDs that don't have a Yes entry (like ID 2), the only row will get row_rank 1 automatically.
• Finally, we filter for rows where row_rank = 1 to get our desired unique records.

Method 2: Using Correlated Subquery (Alternative Approach) #

If your database doesn't support CTEs or window functions (though most modern ones do), this method works by checking if a Yes entry exists for each ID:

SELECT t.*
FROM your_table_name t
WHERE 
    -- Keep all rows where Pregnancy is Yes
    t.Pregnancy = 'Yes'
    -- OR keep rows where there's no Yes entry for the same ID
    OR NOT EXISTS (
        SELECT 1
        FROM your_table_name t2
        WHERE t2.ID = t.ID
        AND t2.Pregnancy = 'Yes'
    );

How this works:

• The first condition grabs all rows with Pregnancy = Yes (these are the ones we want to prioritize).
• The NOT EXISTS clause adds back any rows where the ID doesn't have a Yes entry at all (like ID 2's row), ensuring we don't lose those records.

Both methods will give you the desired result:

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