查询USERS表中COMPANYID相同但地址不匹配的用户记录

Got it, let's tackle this problem. You need to pull all user records where the same COMPANYID has variations in either ADDRESS 1 or ADDRESS 2. Here are two solid, efficient approaches to solve this:

Method 1: Self-Join #

This approach joins the table to itself to compare records within the same company. We look for pairs of users in the same company where their addresses don't match, then use DISTINCT to avoid duplicate records.

SELECT DISTINCT u1.*
FROM USERS u1
JOIN USERS u2 
  ON u1.COMPANYID = u2.COMPANYID
  AND u1.USER != u2.USER -- Skip comparing a user to their own record
WHERE u1.`ADDRESS 1` != u2.`ADDRESS 1` 
   OR u1.`ADDRESS 2` != u2.`ADDRESS 2`;

How it works: #

• We join the USERS table to itself (u1 and u2) on matching COMPANYID values, ensuring we're not checking a user against their own data.
• The WHERE clause flags any pair where either address field doesn't match.
• DISTINCT removes duplicate rows (since each mismatched pair would show up twice, like user 3 paired with user 4 and vice versa).

Method 2: Window Functions (Better for Large Datasets) #

This method uses window functions to count unique address combinations per company, then filters companies with more than one unique combination. It's more efficient for big datasets because it avoids the overhead of a self-join.

WITH CompanyAddressGroups AS (
  SELECT 
    *,
    -- Count unique address pairs per company (use a separator to avoid false matches)
    COUNT(DISTINCT CONCAT(`ADDRESS 1`, '|', `ADDRESS 2`)) OVER (PARTITION BY COMPANYID) AS address_variations
  FROM USERS
)
SELECT USER, COMPANYID, `ADDRESS 1`, `ADDRESS 2`
FROM CompanyAddressGroups
WHERE address_variations > 1;

How it works: #

• The CTE (CompanyAddressGroups) adds a column address_variations that counts how many unique (ADDRESS 1 + ADDRESS 2) pairs exist for each company. The | separator prevents false matches (like "Street A" + "11" vs "Street" + "A11" being treated as the same).
• We then select all records where address_variations > 1—meaning the company has inconsistent addresses across its users.

Sample Result #

Using your provided data, both queries will return these records:

Companies A and D have consistent addresses across all users, so their records are excluded.

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