SQL Server跨表查询：基于姓名与手机号查找重复客户

Got it, let's work through this problem step by step. Your goal is to spot duplicate customers where their forename, surname, and phone number all match, pulling data from both the customers and add tables (note: add is a reserved keyword in SQL Server, so we’ll wrap it in square brackets to avoid syntax errors). Here’s how to adjust your existing query to make this happen:

Key Pre-Requisite #

We need to join the customers and [add] tables using the shared custid column to link each customer to their phone number—this is missing from your original query, which only looks at the customers table.

Solution 1: Extend Your Window Function Approach #

This builds on your existing logic by adding phone number to the partition, then filtering for groups with more than one record.

SELECT 
    custid, 
    forename, 
    surname, 
    phone_number
FROM (
    SELECT 
        c.custid,
        c.forename,
        c.surname,
        a.line1 AS phone_number,
        -- Count customers sharing the exact same name + phone number
        COUNT(*) OVER (PARTITION BY c.forename, c.surname, a.line1) AS duplicate_count
    FROM customers c
    INNER JOIN [add] a 
        ON c.custid = a.custid
) AS customer_duplicate_check
-- Only keep records with at least one duplicate
WHERE duplicate_count > 1
ORDER BY surname, forename;

How This Works:

1. The join links each customer to their phone number from the [add] table.
2. The window function counts how many customers share the exact same forename, surname, and phone number.
3. We filter out records with no duplicates (count = 1) to only keep the duplicates.

Solution 2: Using GROUP BY + HAVING (Alternative Method) #

If you prefer a more explicit grouping approach, this first identifies duplicate name+phone combinations, then joins back to the original tables to get full customer details.

SELECT 
    c.custid,
    c.forename,
    c.surname,
    a.line1 AS phone_number
FROM customers c
INNER JOIN [add] a 
    ON c.custid = a.custid
-- Join with a subquery that finds all duplicate name+phone pairs
INNER JOIN (
    SELECT 
        c.forename,
        c.surname,
        a.line1
    FROM customers c
    INNER JOIN [add] a 
        ON c.custid = a.custid
    GROUP BY c.forename, c.surname, a.line1
    -- Only keep pairs that appear more than once
    HAVING COUNT(*) > 1
) AS duplicate_pairs
    ON c.forename = duplicate_pairs.forename
    AND c.surname = duplicate_pairs.surname
    AND a.line1 = duplicate_pairs.line1
ORDER BY c.surname, c.forename;

How This Works:

1. The subquery groups customers by name and phone number, then uses HAVING COUNT(*) > 1 to isolate only the combinations with duplicates.
2. We join this subquery back to the original tables to retrieve all customer records matching these duplicate combinations.

Optional: Adjust for Partial/Case-Insensitive Matches #

If you need to account for case differences (e.g., "David" vs "david") or partial surname matches (like your original left(surname,3)), tweak the partition/grouping clauses:

• Use LOWER(c.forename) and LOWER(c.surname) for case-insensitive matches.
• Replace c.surname with LEFT(c.surname, 3) to match on the first 3 characters of the surname.

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