基于MySQL表source列生成差异记录对比报告的技术需求

Got it, let's break down how to solve this problem: you need to compare records for the same entity across different source values, and highlight any where fields (other than source itself) don't match. Here's a practical, step-by-step approach using MySQL.

First, Let's Define Our Table Structure #

Assume we have a table named entity_records that stores our data. A simplified version might look like this:

CREATE TABLE entity_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_id VARCHAR(50) NOT NULL, -- Unique identifier for the person/entity (e.g., Joe's ID)
    spouse VARCHAR(100),
    age INT,
    source VARCHAR(50) NOT NULL -- The source of the record (e.g., "CRM", "API")
    -- Add other fields you need to compare here
);

Core SQL Query to Find Mismatches #

This query will compare records for the same entity_id across different sources, flag mismatches, and show the status of each entity:

SELECT 
    er1.entity_id,
    er1.source AS source_1,
    er2.source AS source_2,
    -- Check for mismatches in each field and generate a descriptive message
    CASE 
        WHEN NOT (er1.spouse IS NOT DISTINCT FROM er2.spouse) 
        THEN CONCAT('Spouse mismatch: ', COALESCE(er1.spouse, 'NULL'), ' vs ', COALESCE(er2.spouse, 'NULL')) 
        ELSE NULL 
    END AS spouse_diff,
    CASE 
        WHEN NOT (er1.age IS NOT DISTINCT FROM er2.age) 
        THEN CONCAT('Age mismatch: ', COALESCE(er1.age, 'NULL'), ' vs ', COALESCE(er2.age, 'NULL')) 
        ELSE NULL 
    END AS age_diff,
    'Mismatched' AS status
FROM 
    entity_records er1
JOIN 
    entity_records er2 
    ON er1.entity_id = er2.entity_id 
    AND er1.source < er2.source -- Avoid duplicate pairs (e.g., SourceX vs SourceY and vice versa)
WHERE 
    -- Filter for entities where at least one field doesn't match
    NOT (er1.spouse IS NOT DISTINCT FROM er2.spouse)
    OR NOT (er1.age IS NOT DISTINCT FROM er2.age)
    -- Add more OR conditions for other fields you need to check
UNION ALL
-- Optional: Include entities with only one source (if you want to show their status)
SELECT 
    entity_id,
    source AS source_1,
    NULL AS source_2,
    NULL AS spouse_diff,
    NULL AS age_diff,
    'Single source' AS status
FROM 
    entity_records
GROUP BY 
    entity_id, source
HAVING 
    COUNT(DISTINCT source) = 1
ORDER BY 
    entity_id, source_1, source_2;

Key Details About This Query: #

• Handling NULLs: We use IS NOT DISTINCT FROM (MySQL 8.0+) to properly compare fields that might be NULL (since NULL != NULL doesn't evaluate to true in SQL). If you're on an older MySQL version, you'll need to adjust the logic to account for NULLs explicitly.
• Avoiding Duplicates: The er1.source < er2.source condition ensures we only compare each pair of sources once (no redundant A vs B and B vs A rows).
• Clear Status Labels: Entities with mismatches get a "Mismatched" status, while those with only one source get "Single source" (you can remove this part if you don't need it).

Example Output #

Let's say we have this test data:

INSERT INTO entity_records (entity_id, spouse, age, source) VALUES
('Joe', 'Alice', 35, 'SourceX'),
('Joe', 'Betty', 35, 'SourceY'),
('Bob', 'Charlie', 40, 'SourceX'),
('Bob', 'Charlie', 40, 'SourceY'),
('Mark', 'Diana', 28, 'SourceX'),
('Mark', 'Diana', 28, 'SourceY'),
('Ernest', 'Eve', 50, 'SourceX');

Running the query would return:

As you can see:

• Joe is flagged because his spouse field differs between sources.
• Bob and Mark don't appear in the results since all their fields (excluding source) match across sources.
• Ernest is listed with a "Single source" status since he only has one record.

Extra Tips #

• Dynamic Fields: If you have a lot of fields to compare, you can use dynamic SQL to generate the CASE statements and WHERE conditions automatically (this saves you from writing each field manually).
• Simplified Mismatch Check: If you just need to know which entities have mismatches (not the specific fields), you can group by entity_id and check if the count of distinct combinations of non-source fields is greater than 1.

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