SQL查询中NULL值补偿：获取设备最后测试记录及测试人员

现有两张数据表：

• DvTestResults：存储设备测试记录，包含DVTR_DeviceNo（设备编号）、DVTR_TestedOnAt（测试时间）、DVTR_TesterNo（测试人员编号）字段
• Tester：存储测试人员信息，包含T_TesterNo（测试人员编号）、T_TesterName（测试人员姓名）字段

数据表详情 #

DvTestResults表 #

Tester表 #

需求说明 #

获取每个设备的最后测试时间、测试人员，按设备号排序且无重复设备，规则如下：

• 若最后测试记录的DVTR_TesterNo不为NULL，直接取对应测试人员姓名
• 若最后测试记录的DVTR_TesterNo为NULL，优先取该设备最近有记录的测试人员（如DV00001需显示John）
• 若设备所有测试记录的DVTR_TesterNo均为NULL，则显示“Anonymous”（如DV00005）

原SQL问题 #

原查询仅能返回最后测试记录中DVTR_TesterNo非NULL的设备，无法处理NULL场景：

SELECT DvTestResults.DVTR_DeviceNo, max.LastTimeTested, Tester.T_TesterName 
FROM DvTestResults
INNER JOIN
(
    SELECT DVTR_DeviceNo, MAX(DVTR_TestedOnAt) as LastTimeTested 
    FROM DvTestResults
    GROUP BY DVTR_DeviceNo
) as max
    on max.DVTR_DeviceNo = DvTestResults.DVTR_DeviceNo and max.LastTimeTested = DvTestResults.DVTR_TestedOnAt
INNER JOIN Tester ON Tester.TesterNo = DvTestResults.DVTR_TesterNo
ORDER BY DvTestResults.DVTR_DeviceNo

优化后的SQL方案 #

方案一：基于窗口函数标记优先级 #

WITH RankedRecords AS (
    SELECT 
        DVTR_DeviceNo,
        DVTR_TestedOnAt,
        DVTR_TesterNo,
        -- 优先级排序：1=最后测试时间且有测试人员；2=最后测试时间无测试人员；3=最近有测试人员的记录；4=其他
        ROW_NUMBER() OVER (
            PARTITION BY DVTR_DeviceNo 
            ORDER BY 
                CASE WHEN DVTR_TestedOnAt = (SELECT MAX(DVTR_TestedOnAt) FROM DvTestResults dr WHERE dr.DVTR_DeviceNo = d.DVTR_DeviceNo) THEN 1 ELSE 2 END,
                CASE WHEN DVTR_TesterNo IS NOT NULL THEN 1 ELSE 2 END,
                DVTR_TestedOnAt DESC
        ) AS rn
    FROM DvTestResults d
)
SELECT 
    rr.DVTR_DeviceNo,
    (SELECT MAX(DVTR_TestedOnAt) FROM DvTestResults dr WHERE dr.DVTR_DeviceNo = rr.DVTR_DeviceNo) AS LastTimeTested,
    COALESCE(t.T_TesterName, 'Anonymous') AS TesterName
FROM RankedRecords rr
LEFT JOIN Tester t ON rr.DVTR_TesterNo = t.T_TesterNo
WHERE rr.rn = 1
ORDER BY rr.DVTR_DeviceNo;

方案二：拆分获取最后测试时间与有效测试人员 #

WITH DeviceLastTest AS (
    -- 获取每个设备的最后测试时间
    SELECT 
        DVTR_DeviceNo,
        MAX(DVTR_TestedOnAt) AS LastTimeTested
    FROM DvTestResults
    GROUP BY DVTR_DeviceNo
),
DeviceLastTester AS (
    -- 获取每个设备最近的有效测试人员编号
    SELECT 
        DVTR_DeviceNo,
        FIRST_VALUE(DVTR_TesterNo) OVER (
            PARTITION BY DVTR_DeviceNo 
            ORDER BY CASE WHEN DVTR_TesterNo IS NOT NULL THEN 1 ELSE 2 END, DVTR_TestedOnAt DESC
        ) AS LastValidTesterNo
    FROM DvTestResults
)
SELECT 
    dlt.DVTR_DeviceNo,
    dlt.LastTimeTested,
    COALESCE(t.T_TesterName, 'Anonymous') AS TesterName
FROM DeviceLastTest dlt
JOIN DeviceLastTester dlt2 ON dlt.DVTR_DeviceNo = dlt2.DVTR_DeviceNo
LEFT JOIN Tester t ON dlt2.LastValidTesterNo = t.T_TesterNo
GROUP BY dlt.DVTR_DeviceNo, dlt.LastTimeTested, t.T_TesterName
ORDER BY dlt.DVTR_DeviceNo;

逻辑说明 #

1. 方案一：通过窗口函数给每条记录标记优先级，优先筛选出符合需求的记录，再关联测试人员表处理空值
2. 方案二：拆分两个CTE，分别获取设备最后测试时间、最近有效测试人员编号，最后关联并通过COALESCE处理全空场景，显示“Anonymous”

两种方案均能覆盖所有场景，返回符合需求的结果。

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