SQL Server 2008中如何基于两张表优先获取日志表数据

Got it, let's work through this. Since you can't join the two tables directly, the key here is to combine the data from both tables, mark the source to prioritize the log table, then pick the right record for each RegistrationID.

First, I'll assume your other table (the original one, not the log) has a similar structure to Table_AttachLog—let's call it Table_Attach for this example. If the table name is different, just swap it out in the code.

Here's the query that does exactly what you need:

WITH CombinedData AS (
    -- Get all records from the log table, mark with priority 1
    SELECT 
        RegistrationID,
        uploadedFile,
        updateddate,
        1 AS IsLog  -- Higher priority for log table
    FROM Table_AttachLog

    UNION ALL

    -- Get all records from the original table, mark with priority 0
    SELECT 
        RegistrationID,
        uploadedFile,
        updateddate,
        0 AS IsLog
    FROM Table_Attach
),
RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY RegistrationID 
            ORDER BY IsLog DESC, updateddate DESC
        ) AS rn
    FROM CombinedData
)
SELECT 
    RegistrationID,
    uploadedFile,
    updateddate
FROM RankedData
WHERE rn = 1;

Breakdown of how this works: #

• UNION ALL: This merges data from both tables without removing duplicates (which we don't want here, since we need to compare records for the same RegistrationID).
• IsLog field: We add this to explicitly mark which table each record comes from. Log table records get 1 (higher priority), original table gets 0.
• ROW_NUMBER() Window Function:
  • PARTITION BY RegistrationID groups records by each unique ID.
  • ORDER BY IsLog DESC ensures log table records are ranked first for each ID.
  • updateddate DESC then picks the most recent record if there are multiple entries from the same table.
• Final Filter: WHERE rn = 1 selects only the top-ranked record for each RegistrationID—so you get the log table's latest record if it exists, otherwise the latest from the original table.

If your original table has a different name or column names, just adjust the query to match your actual schema.

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