基于多OR条件的SQL分组：人员表重复记录识别方案问询

Hey there, let's tackle this duplicate grouping problem head-on. Your requirement to group records when they match on any of those three name-plus-attribute combinations falls into the connected components category in SQL, and recursive CTEs are the way to go here—they're also flexible enough to handle your stricter future rules without a full rewrite.

Core Logic Breakdown #

You need to group records if they meet ANY of these criteria:

• fname + lname + address are identical
• fname + lname + dob are identical
• fname + lname + email are identical

Recursive CTEs let us build out these groups incrementally: first starting with each record as its own group, then merging groups whenever a matching rule is met, until no more merges are possible.

SQL Implementation #

Let's assume your table is named people with columns id, fname, lname, address, dob, email. Here's the full query:

WITH RECURSIVE person_groups AS (
    -- Step 1: Initialize each record as its own group
    SELECT
        id,
        fname,
        lname,
        address,
        dob,
        email,
        id AS group_id
    FROM people

    UNION ALL

    -- Step 2: Recursively merge groups that match any of your rules
    SELECT
        p.id,
        p.fname,
        p.lname,
        p.address,
        p.dob,
        p.email,
        pg.group_id
    FROM people p
    JOIN person_groups pg ON
        -- Rule 1: Name + Address match
        (p.fname = pg.fname AND p.lname = pg.lname AND p.address = pg.address)
        -- Rule 2: Name + Date of Birth match
        OR (p.fname = pg.fname AND p.lname = pg.lname AND p.dob = pg.dob)
        -- Rule 3: Name + Email match
        OR (p.fname = pg.fname AND p.lname = pg.lname AND p.email = pg.email)
    WHERE p.id NOT IN (SELECT id FROM person_groups)
),
-- Step 3: Deduplicate and assign a consistent group ID (using the smallest ID in the group)
final_groups AS (
    SELECT
        id,
        MIN(group_id) OVER (PARTITION BY id) AS final_group_id
    FROM person_groups
)
-- Final output: All records with their assigned group ID
SELECT
    p.*,
    fg.final_group_id
FROM people p
JOIN final_groups fg ON p.id = fg.id
ORDER BY fg.final_group_id, p.id;

Adapting for Stricter Future Rules #

When you need to add more strict grouping conditions (like matching on phone number, or a combination of multiple attributes), it's easy to extend the query—just add another OR clause in the recursive join condition. For example:

OR (p.fname = pg.fname AND p.lname = pg.lname AND p.phone = pg.phone)

Key Notes #

• The recursive step keeps merging groups until no new records can be added to existing groups
• Using MIN(group_id) ensures every record in the same group gets a consistent, unique group identifier
• This approach works for even the most complex multi-condition grouping rules you might need later

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