SQL查询：找出不同行数据差异并生成结构化变更记录表

Hey there! Let's tackle this change tracking problem step by step. First, let's recap the scenario to make sure we're on the same page:

Initial Audit Table #

We have an audit table that logs every save action (even when no fields are actually modified) with the user, timestamp, and current state of all data fields. Here's what it looks like:

INITIAL TABLE -------------------------------------------------------
ID  Date                Author        Data1  Data9
==  ================    ==========    ====== ======
1   2017-06-04 16:47    John Smith    Foo    Bar
2   2017-06-04 16:50    John Smith    Goo    Bar
3   2017-06-04 16:52    John Smith    Hoo    Car
4   2017-06-04 16:55    Bill Plith    Foo    Bar
5   2017-06-04 16:59    John Smith    Foo    Car
6   2017-06-04 17:04    Bill Plith    Foo    Bar

Goal #

We need to convert this into a clean change log that highlights only actual field modifications, groups changes by the same user and timestamp, and maintains the original order based on ID (not Date, since IDs might have gaps). The target output looks like this:

CHANGE TABLE --------------------------------------------------------
Date                Author        Changes
===============     ==========    ================================
2017-06-04 16:50    John Smith    Data1 was changed to Goo
2017-06-04 16:52    John Smith    Data1 was changed to Hoo, Data9 was changed to Car
2017-06-04 16:55    Bill Plith    Data1 was changed to Foo, Data9 was changed to Bar
2017-06-04 16:59    John Smith    Data9 was changed to Car
2017-06-04 17:04    Bill Plith    Data9 was changed to Bar

Requirements #

• Skip any rows where no Data1-Data9 fields changed from the previous row (ordered by ID)
• Combine multiple changes for the same Date and Author into a comma-separated list
• Order results based on ID (not Date) since IDs can have gaps

Solution SQL Query #

Since you're learning SQL, I'll break this down into manageable CTEs (Common Table Expressions) to make it easy to follow. Note: This uses standard SQL functions—adjustments might be needed for specific dialects (like MySQL, SQL Server) which I'll mention later.

WITH ranked_audit AS (
    -- Create a sequential row number ordered by ID (handles ID gaps)
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY ID) AS row_num
    FROM initial_table
),
previous_row_data AS (
    -- Pull values from the immediately preceding row (using row_num)
    SELECT
        curr.ID,
        curr.Date,
        curr.Author,
        -- Get previous values for each Data field
        LAG(curr.Data1) OVER (ORDER BY curr.row_num) AS prev_Data1,
        LAG(curr.Data9) OVER (ORDER BY curr.row_num) AS prev_Data9,
        -- Add lines here for Data2 to Data8 if they exist
        curr.Data1,
        curr.Data9
        -- Add lines here for Data2 to Data8 if they exist
    FROM ranked_audit curr
),
individual_changes AS (
    -- Generate a change entry for each modified field
    SELECT Date, Author, 
           CASE WHEN Data1 != prev_Data1 THEN 'Data1 was changed to ' || Data1 END AS change_desc
    FROM previous_row_data
    WHERE row_num > 1 -- Skip first row (no prior row to compare)
    
    UNION ALL -- Combine with changes from other Data fields
    
    SELECT Date, Author, 
           CASE WHEN Data9 != prev_Data9 THEN 'Data9 was changed to ' || Data9 END AS change_desc
    FROM previous_row_data
    WHERE row_num > 1
    
    -- Add UNION ALL blocks here for Data2 to Data8 if they exist
),
filtered_changes AS (
    -- Remove entries where no change occurred (NULL change_desc)
    SELECT Date, Author, change_desc
    FROM individual_changes
    WHERE change_desc IS NOT NULL
)
-- Aggregate changes by Date and Author, order by original ID sequence
SELECT
    Date,
    Author,
    STRING_AGG(change_desc, ', ') AS Changes
FROM filtered_changes
GROUP BY Date, Author
-- Order by the earliest ID for each Date/Author group to maintain ID-based sequence
ORDER BY (SELECT MIN(ID) FROM initial_table WHERE Date = filtered_changes.Date AND Author = filtered_changes.Author);

Step-by-Step Explanation #

1. ranked_audit: We use ROW_NUMBER() ordered by ID to create a sequential numbering. This fixes issues with missing IDs—we can reliably get the previous row even if IDs aren't consecutive.
2. previous_row_data: The LAG() window function pulls the value of each Data field from the row that came right before the current one (using our row_num sequence). This lets us compare current vs. previous values easily.
3. individual_changes: For each Data field, we check if the current value differs from the previous. If it does, we generate a human-readable change string. We use UNION ALL to stack these change entries from all fields into a single list.
4. filtered_changes: We filter out any rows where no change was detected (where change_desc is NULL) to meet requirement #1.
5. Final Aggregation: We group by Date and Author, using STRING_AGG() to combine multiple changes into a single comma-separated string. The ORDER BY clause uses the minimum ID for each group to ensure we follow the original ID-based sequence.

Dialect-Specific Adjustments #

• MySQL: Replace STRING_AGG(change_desc, ', ') with GROUP_CONCAT(change_desc SEPARATOR ', ')
• SQL Server: STRING_AGG works in SQL Server 2017 and later—if you're on an older version, you'll need to use a workaround like FOR XML PATH
• Oracle: Use LISTAGG(change_desc, ', ') WITHIN GROUP (ORDER BY change_desc) instead of STRING_AGG

Notes for Your Learning #

• Make sure to add all your Data fields (Data2 to Data8) in the previous_row_data and individual_changes sections—this example only includes Data1 and Data9 as in your sample.
• The first row in the initial table is skipped because there's no prior row to compare against, which matches your target output.

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