SQL Server按最近时间戳自连接匹配同人员同物质两种检测结果的方法

你的现有代码的核心问题是仅做了同人员、同检测物质的两类方法的全量关联，没有对时间差做筛选，会产生大量冗余匹配，也无法定位到最近时间戳的记录。针对SQL Server环境，我们可以用OUTER APPLY + 时间差排序取TOP 1的逻辑实现需求，相比窗口函数写法性能更优，更适配百万级数据量的场景。

WITH PreprocessData AS (
    -- 先预处理所有检测数据，拆分出物质类型和检测方法，避免后续重复写CASE逻辑
    SELECT 
        T.DateTime,
        T.PersonID,
        T.SampleID,
        A.Analysis,
        T.Result,
        -- 提取物质名称
        CASE 
            WHEN A.Analysis LIKE '%\_A' ESCAPE '\' OR A.Analysis LIKE '%\_B' ESCAPE '\' 
            THEN LEFT(A.Analysis, LEN(A.Analysis) - 2)
            ELSE NULL 
        END AS MaterialName,
        -- 提取检测方法
        CASE 
            WHEN A.Analysis LIKE '%\_A' ESCAPE '\' THEN 'A'
            WHEN A.Analysis LIKE '%\_B' ESCAPE '\' THEN 'B'
            ELSE NULL 
        END AS TestMethod
    FROM [DataTable] T
    LEFT JOIN [AnalysisTable] A ON A.DW_SK_Analyse = T.DW_SK_Analyse
    WHERE A.Analysis IN ('Sodium_A','Potassium_A','Sodium_B','Potassium_B') -- 过滤有效检测项，减少数据量
)
SELECT 
    -- A方法检测的字段
    T1.DateTime AS A_DateTime,
    T1.PersonID,
    T1.SampleID AS A_SampleID,
    T1.Analysis AS A_Analysis,
    T1.Result AS A_Result,
    -- 匹配到的最近时间B方法检测的字段
    T2.DateTime AS B_DateTime,
    T2.SampleID AS B_SampleID,
    T2.Analysis AS B_Analysis,
    T2.Result AS B_Result
FROM PreprocessData T1
-- 关联同人员、同物质的B方法检测，取时间最接近的1条
OUTER APPLY (
    SELECT TOP 1 *
    FROM PreprocessData T2
    WHERE 
        T2.PersonID = T1.PersonID
        AND T2.MaterialName = T1.MaterialName
        AND T2.TestMethod = 'B'
        AND T1.TestMethod = 'A' -- 限定左表是A方法，右表是B方法
    ORDER BY ABS(DATEDIFF(SECOND, T1.DateTime, T2.DateTime)) ASC, T2.DateTime ASC -- 时间差最小优先，差相同时取更早的B记录避免重复
) T2
WHERE T1.TestMethod = 'A' -- 限定左表仅为A方法的检测记录

• 先用CTE做数据预处理，统一拆分物质名称和检测方法，同时过滤无效检测项，大幅减少后续关联的数据量
• OUTER APPLY会为每一条A方法的检测记录，匹配同人员同物质下的B方法记录，按时间差绝对值排序后取第一条，直接实现最近时间戳匹配
• 如需调整匹配规则（比如只允许B方法时间在A之后），可在APPLY的WHERE条件中添加T2.DateTime >= T1.DateTime即可
• 性能优化建议：给DataTable的PersonID、DW_SK_Analyse、DateTime字段加联合索引，AnalysisTable的DW_SK_Analyse加主键索引，百万级数据也能快速返回结果

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