无需使用最后修改日期，如何查询表中最近变更的SQL列？

Got it, let's solve this problem. Since you don't have a last_modified_date column to track updates, we need to directly compare the original and modified versions of your employee table to pinpoint exactly which columns were changed.

First, let's clarify the setup: I’ll assume your original table is named original_employees and the modified version is modified_employees, with S.No as the unique primary key to match rows between the two tables.

Approach 1: Direct Row Comparison + Column-Level Check #

First, we can flag all rows that have any changes, then dig into which specific columns were altered.

Step 1: Find All Rows with Changes #

This query returns all rows that differ between the original and modified tables (either missing from one table or having differing values):

-- Get all rows that exist in one table but not the other, or have value differences
SELECT * FROM original_employees
EXCEPT
SELECT * FROM modified_employees
UNION ALL
SELECT * FROM modified_employees
EXCEPT
SELECT * FROM original_employees;

Step 2: Pinpoint Exact Changed Columns #

Once we know which rows changed, we can compare each column individually to see what’s different. This query lists the S.No of the changed row and all columns that were updated:

SELECT
    o."S.No",
    STRING_AGG(changed_col, ', ') AS updated_columns
FROM (
    -- Check Employee id
    SELECT
        o."S.No",
        'Employee id' AS changed_col
    FROM original_employees o
    JOIN modified_employees m ON o."S.No" = m."S.No"
    WHERE o."Employee id" <> m."Employee id"
    
    UNION ALL
    
    -- Check First Name
    SELECT
        o."S.No",
        'First Name' AS changed_col
    FROM original_employees o
    JOIN modified_employees m ON o."S.No" = m."S.No"
    WHERE o."First Name" <> m."First Name"
    
    UNION ALL
    
    -- Check Last Name
    SELECT
        o."S.No",
        'Last Name' AS changed_col
    FROM original_employees o
    JOIN modified_employees m ON o."S.No" = m."S.No"
    WHERE o."Last Name" <> m."Last Name"
    
    UNION ALL
    
    -- Check Address
    SELECT
        o."S.No",
        'Address' AS changed_col
    FROM original_employees o
    JOIN modified_employees m ON o."S.No" = m."S.No"
    WHERE o."Address" <> m."Address"
) AS column_changes
GROUP BY o."S.No";

For your sample data, this would return:

• S.No 1: First Name
• S.No 2: First Name
• S.No 4: Address

Approach 2: Hash-Based Row Comparison (Faster for Large Tables) #

If your table is large, calculating a hash for each row can quickly narrow down which rows have changes, then we can check the columns for those rows.

Step 1: Identify Changed Rows with Hashes #

We’ll create a hash of all columns in each row, then compare hashes between the original and modified tables:

-- Find rows where the row-level hash differs (indicating a change)
SELECT o."S.No"
FROM original_employees o
JOIN modified_employees m ON o."S.No" = m."S.No"
WHERE 
    MD5(CONCAT(
        COALESCE(o."Employee id", ''), '|',
        COALESCE(o."First Name", ''), '|',
        COALESCE(o."Last Name", ''), '|',
        COALESCE(o."Address", '')
    )) 
    != 
    MD5(CONCAT(
        COALESCE(m."Employee id", ''), '|',
        COALESCE(m."First Name", ''), '|',
        COALESCE(m."Last Name", ''), '|',
        COALESCE(m."Address", '')
    ));

Note: We use COALESCE to handle NULL values, since CONCAT treats NULL as an empty string by default in some databases. Adjust the hash function (e.g., SHA256 instead of MD5) based on your database system.

Step 2: Check Columns for Changed Rows #

Once you have the list of S.No values from the above query, use the column-level check query from Approach 1 filtered to those rows to get the exact updated columns.

Important Notes #

• Case Sensitivity: String comparisons may be case-sensitive depending on your database's collation. If you don’t care about case, use a function like LOWER() when comparing string columns.
• NULL Handling: The queries above use COALESCE to avoid issues with NULL values, but adjust this based on how your database treats NULL comparisons.
• Database-Specific Functions: Hash functions (like MD5) and string aggregation (like STRING_AGG) may vary by database (e.g., GROUP_CONCAT in MySQL instead of STRING_AGG in PostgreSQL/SQL Server).

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