SQL中去重记录并保留对应id与entCode的实现方法

Got it, let's sort this out. You've already nailed the address grouping part to eliminate duplicates, and now you just need to pull in the first corresponding id and entCode for each unique address set. Here are two reliable approaches depending on your database's capabilities:

1. Using Window Functions (Cleanest Modern Approach) #

Most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.) support window functions, which make this task straightforward. We'll use ROW_NUMBER() to assign a rank to each row within its address group, then pick the first entry:

WITH ranked_addresses AS (
    SELECT 
        id,
        entCode,
        postCode,
        addressLine1,
        addressLine2,
        addressLine3,
        addressLine4,
        addressLine5,
        -- Assign a row number to each entry in the same address group, ordered by id to get the earliest record
        ROW_NUMBER() OVER (
            PARTITION BY postCode, addressLine1, addressLine2, addressLine3, addressLine4, addressLine5
            ORDER BY id ASC
        ) AS row_rank
    FROM addresses
)
SELECT 
    id,
    entCode,
    postCode,
    addressLine1,
    addressLine2,
    addressLine3,
    addressLine4,
    addressLine5
FROM ranked_addresses
WHERE row_rank = 1;

Quick Notes: #

• If "first" refers to the earliest inserted record instead of the smallest id, swap ORDER BY id ASC with your timestamp column (e.g., ORDER BY created_at ASC).
• The PARTITION BY clause includes all your address fields to ensure we're grouping exactly on unique address combinations.

2. Using Subquery + Join (For Older Databases) #

If you're working with a database that doesn't support window functions (like MySQL 5.x), this approach uses a grouped subquery to find the minimum id per address group, then joins back to the original table to fetch the full record:

SELECT 
    main.id,
    main.entCode,
    main.postCode,
    main.addressLine1,
    main.addressLine2,
    main.addressLine3,
    main.addressLine4,
    main.addressLine5
FROM addresses main
INNER JOIN (
    -- Get the smallest id for each unique address combination
    SELECT 
        postCode,
        addressLine1,
        addressLine2,
        addressLine3,
        addressLine4,
        addressLine5,
        MIN(id) AS first_id
    FROM addresses
    GROUP BY postCode, addressLine1, addressLine2, addressLine3, addressLine4, addressLine5
) grouped_addresses ON 
    main.postCode = grouped_addresses.postCode
    AND main.addressLine1 = grouped_addresses.addressLine1
    AND main.addressLine2 = grouped_addresses.addressLine2
    AND main.addressLine3 = grouped_addresses.addressLine3
    AND main.addressLine4 = grouped_addresses.addressLine4
    AND main.addressLine5 = grouped_addresses.addressLine5
    AND main.id = grouped_addresses.first_id;

Quick Notes: #

• This uses MIN(id) to identify the "first" record in each group. If you need to prioritize a different field (like the smallest entCode), replace MIN(id) with MIN(entCode) and adjust the join condition to match.

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