SQL Server：用倒数第二行结果更新最后一行且兼容单行场景

Hey, let's tackle this problem step by step. Your goal is to update the Pass and Fail fields of the most recent record (for TestaufstellungID = 3166) with the values from the second-most recent one, and make sure the query doesn't error out if there's only one matching record. Let's break this down.

What's Wrong with the Original Code? #

Your current query tries to exclude the max TestergebnisID to get the second-last record, but it has two main issues:

• If there's only one record, the subqueries return NULL, which would set Pass/Fail to NULL (not what you want—you just want no update to happen).
• You mentioned it's using the first record's values instead of the second-last, which might be due to inconsistent ordering or filtering logic.

The Best Solution for SQL Server 2005 (Using Window Functions) #

Since you're on SQL Server 2005 (version 9.0.5057), we can use the ROW_NUMBER() window function to clearly rank records by their TestergebnisID in descending order. This makes it easy to target the last (rank 1) and second-last (rank 2) records.

Here's the corrected code:

WITH RankedResults AS (
    SELECT 
        TestergebnisID,
        Pass,
        Fail,
        -- Rank records in descending order of TestergebnisID for TestaufstellungID = 3166
        ROW_NUMBER() OVER (PARTITION BY TestaufstellungID ORDER BY TestergebnisID DESC) AS RowNum
    FROM DB.dbo.Testergebnisse
    WHERE TestaufstellungID = 3166
)
UPDATE t1
SET 
    t1.Pass = t2.Pass,
    t1.Fail = t2.Fail
FROM RankedResults t1
INNER JOIN RankedResults t2 
    ON t1.RowNum = 1 -- Target the last record
    AND t2.RowNum = 2 -- Get values from the second-last record
WHERE t1.RowNum = 1;

How This Works #

1. CTE RankedResults: This assigns a rank to each matching record. The newest record gets RowNum = 1, the second-newest RowNum = 2, and so on.
2. INNER JOIN: We only join the last record with the second-last one. If there's only one record, the join returns no rows—so the UPDATE does nothing, no errors.
3. Precise Update: Only the last record is updated, and only if a second-last record exists.

Verify Before Updating #

Before running the update, you can check the rankings to make sure everything is correct:

SELECT 
    TestergebnisID,
    Pass,
    Fail,
    ROW_NUMBER() OVER (PARTITION BY TestaufstellungID ORDER BY TestergebnisID DESC) AS RowNum
FROM DB.dbo.Testergebnisse
WHERE TestaufstellungID = 3166
ORDER BY RowNum;

This will show you exactly which records are ranked 1 and 2, so you can confirm the values you're about to use.

Alternative (If You Avoid Window Functions) #

If for some reason you don't want to use a CTE, you can use a subquery to get the second-last record's values, but you need to add a check for at least two records to avoid NULL updates:

UPDATE DB.dbo.Testergebnisse
SET 
    Pass = (
        SELECT TOP 1 Pass 
        FROM DB.dbo.Testergebnisse 
        WHERE TestaufstellungID = 3166
        AND TestergebnisID < (SELECT MAX(TestergebnisID) FROM DB.dbo.Testergebnisse WHERE TestaufstellungID = 3166)
        ORDER BY TestergebnisID DESC
    ),
    Fail = (
        SELECT TOP 1 Fail 
        FROM DB.dbo.Testergebnisse 
        WHERE TestaufstellungID = 3166
        AND TestergebnisID < (SELECT MAX(TestergebnisID) FROM DB.dbo.Testergebnisse WHERE TestaufstellungID = 3166)
        ORDER BY TestergebnisID DESC
    )
WHERE 
    TestergebnisID = (SELECT MAX(TestergebnisID) FROM DB.dbo.Testergebnisse WHERE TestaufstellungID = 3166)
AND (SELECT COUNT(*) FROM DB.dbo.Testergebnisse WHERE TestaufstellungID = 3166) >= 2;

This works too, but the window function approach is cleaner and easier to maintain.

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