MySQL联合查询及去除半相似行问题咨询

Alright, let's break down how to fix this duplicate record problem you're facing. You've got two tables—mortality (one row max per patient) and clinical timeline (multiple rows per patient)—and death info can live in either, both, or just one. Since tiny differences like the source table flag are messing up DISTINCT, we need smarter ways to deduplicate these semi-similar rows.

Method 1: Prioritize Records with Window Functions (Best for Single Source Preference) #

If you want to keep one specific source's record (e.g., always prefer the mortality table over the timeline when both exist), use ROW_NUMBER() to rank records per patient and death event, then pick the top-ranked one.

WITH combined_records AS (
    SELECT
        patient_id,
        death_date,
        death_cause,
        other_clinical_details,
        source_table,
        -- Rank records: mortality gets priority (1), timeline gets 2
        ROW_NUMBER() OVER (
            PARTITION BY patient_id, DATE(death_date)  -- Group by patient + date (ignore time if needed)
            ORDER BY CASE source_table WHEN 'mortality' THEN 1 ELSE 2 END
        ) AS record_rank
    FROM (
        -- Combine both tables with UNION ALL (faster than UNION since we handle deduplication)
        SELECT 
            patient_id, death_date, death_cause, other_fields, 'mortality' AS source_table
        FROM mortality
        UNION ALL
        SELECT 
            patient_id, death_date, death_cause, other_fields, 'clinical_timeline' AS source_table
        FROM clinical_timeline
        WHERE event_type = 'death'  -- Filter timeline to only death events first!
    ) AS unioned_data
)
-- Keep only the top-ranked record per patient/death date
SELECT *
FROM combined_records
WHERE record_rank = 1;

Why this works: #

• PARTITION BY patient_id, DATE(death_date) groups together all death records for the same patient on the same day (adjust if you need to match exact timestamps instead).
• The ORDER BY clause ensures the mortality table's record is ranked first. If a patient only has a timeline record, it will still be kept as rank 1.

Method 2: Merge Fields from Duplicate Records (Best for Combining Data) #

If you want to keep the most complete data by merging fields from both tables (e.g., take death cause from mortality and notes from the timeline), use aggregate functions to combine values.

SELECT
    patient_id,
    -- Use mortality's date if available, otherwise fall back to timeline
    COALESCE(MAX(mortality_death_date), MAX(timeline_death_date)) AS final_death_date,
    -- Merge death causes (pick non-null value)
    COALESCE(MAX(mortality_death_cause), MAX(timeline_death_cause)) AS final_death_cause,
    -- Combine notes from both sources (separated by semicolon)
    STRING_AGG(DISTINCT notes, '; ') AS combined_notes
FROM (
    SELECT
        patient_id,
        death_date AS mortality_death_date,
        death_cause AS mortality_death_cause,
        NULL AS timeline_death_date,
        NULL AS timeline_death_cause,
        notes
    FROM mortality
    UNION ALL
    SELECT
        patient_id,
        NULL AS mortality_death_date,
        NULL AS mortality_death_cause,
        death_date AS timeline_death_date,
        death_cause AS timeline_death_cause,
        notes
    FROM clinical_timeline
    WHERE event_type = 'death'
) AS unioned_data
GROUP BY patient_id;

Why this works: #

• We split fields from each table into separate columns first, then use COALESCE to pick the preferred value (mortality first) and STRING_AGG to combine supplementary info like notes.

Method 3: Exclude Timeline Records if Mortality Exists (Best for Strict Single Record Per Patient) #

If you just want one death record per patient—period—and you don't need timeline records if the mortality table already has one, use a NOT EXISTS clause to filter out redundant timeline entries.

-- Get all mortality records first
SELECT * FROM mortality
UNION ALL
-- Get only the latest timeline death record for patients NOT in mortality
SELECT t.*
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY record_created DESC) AS latest_rank
    FROM clinical_timeline
    WHERE event_type = 'death'
) t
WHERE t.latest_rank = 1
AND NOT EXISTS (
    SELECT 1 FROM mortality m
    WHERE m.patient_id = t.patient_id
);

Why this works: #

• We first take all mortality records, then add only the most recent timeline death record for patients who don't have an entry in the mortality table. No duplicates, guaranteed.

Pick the method that fits your exact needs—whether it's prioritizing a source, merging data, or just ensuring one record per patient.

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