如何将含ID_expired与ID_issued的表重排为唯一行以关联个体ID历史

Hey, this sounds like a common ID lineage tracking problem I’ve tackled before—let’s break down how to get all linked IDs into a single row for each individual, while cleaning up the messy data you mentioned.

First, let’s recap your scenario to make sure I’m aligned:

• You have a table with ID_expired (old ID) and ID_issued (new ID) that tracks all ID associations
• Issues include duplicate rows, and ID_issued entries with no matching ID_expired (like b111)
• Goal: Group every ID belonging to the same individual into one row for easy historical queries

Step 1: Clean Up Duplicate Rows #

First, we need to eliminate exact duplicate rows to avoid redundant processing. Create a cleaned temporary table (adjust syntax for your database):

-- PostgreSQL example (use CREATE TEMPORARY TABLE for MySQL)
CREATE TEMP TABLE cleaned_id_links AS
SELECT DISTINCT ID_expired, ID_issued
FROM your_source_table;

Step 2: Recursively Traverse ID Chains #

Since IDs are linked in a chain (e.g., g123 → z234 → maybe another new ID), we’ll use a recursive CTE to trace the full lineage for each ID. This also handles the standalone ID_issued entries (like b111) that have no matching ID_expired:

WITH RECURSIVE id_chain AS (
    -- Anchor: Start with all linked ID pairs (old → new)
    SELECT 
        ID_issued AS current_id,
        ID_expired AS prev_id,
        ARRAY[ID_issued, ID_expired] AS id_lineage  -- Store chain as array (PostgreSQL)
    FROM cleaned_id_links
    WHERE ID_expired IS NOT NULL
    UNION ALL
    -- Recursive step: Keep tracing backward to find older IDs in the chain
    SELECT 
        ic.current_id,
        cil.ID_expired,
        ic.id_lineage || cil.ID_expired
    FROM id_chain ic
    JOIN cleaned_id_links cil ON ic.prev_id = cil.ID_issued
),
-- Combine full chains with standalone IDs
full_lineages AS (
    SELECT 
        current_id,
        -- Deduplicate and sort the lineage to avoid duplicates
        ARRAY(SELECT DISTINCT unnest(id_lineage) ORDER BY unnest(id_lineage)) AS all_ids
    FROM id_chain
    UNION
    -- Add standalone IDs with no expired match (like b111)
    SELECT 
        ID_issued AS current_id,
        ARRAY[ID_issued] AS all_ids
    FROM cleaned_id_links
    WHERE ID_expired IS NULL
)
-- Final aggregation: Turn the array into a comma-separated string for readability
SELECT 
    current_id AS primary_id,  -- Use any ID from the chain as the identifier
    STRING_AGG(id, ', ') AS full_id_history
FROM full_lineages, unnest(all_ids) AS id
GROUP BY primary_id;

Database-Specific Adjustments: #

• MySQL 8.0+: Replace arrays with GROUP_CONCAT and adjust recursive CTE syntax (no array support).
• SQL Server: Use STRING_AGG and STRING_SPLIT instead of array functions.

Step 3: Handle Duplicate ID_issued Entries #

If duplicate ID_issued entries (with no ID_expired) belong to the same individual, you’ll need to add a business rule to group them (e.g., matching user metadata if available). If they’re separate, the above query will keep them as distinct rows, which is likely correct.

Example Output #

After running the query, you’ll get results like this (matching your examples):

This lets you quickly look up every ID associated with a single individual in one row.

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