SQL日期关联返回重复行问题排查及代码修正请求

你的核心问题是同一测量记录匹配到了多条符合日期条件的旧限值，原代码的GROUP BY因为包含了LowerLimit和UpperLimit，导致不同的限值会被分成独立的分组，无法只保留最新的那条有效限值。下面我给你两种可行的修正方案，都能精准匹配到每个测量记录对应的最新有效限值。

方法一：先获取最新限值日期，再关联限值表 #

这种方法分两步：先找到每个测量记录对应的最新限值日期，再通过这个日期关联回限值表获取具体的上下限，逻辑清晰易维护。

WITH ValidLimits AS (
    -- 第一步：筛选有效限值，排除上下限均为0或空白的记录
    SELECT 
        Parameter,
        ParameterLimitDate,
        LowerLimit,
        UpperLimit
    FROM dbo.Limits
    WHERE 
        NOT (ISNULL(LowerLimit, 0) = 0 AND ISNULL(UpperLimit, 0) = 0)
),
DataWithLatestLimit AS (
    -- 第二步：对每个测量记录，找到对应参数在测量日期前的最新限值日期
    SELECT 
        d.Batch,
        d.Parameter,
        d.MeasurementDate,
        d.Value,
        MAX(vl.ParameterLimitDate) AS LatestLimitDate
    FROM dbo.Data d
    LEFT JOIN ValidLimits vl 
        ON d.Parameter = vl.Parameter 
        AND vl.ParameterLimitDate <= d.MeasurementDate
    GROUP BY d.Batch, d.Parameter, d.MeasurementDate, d.Value
)
-- 第三步：关联回限值表，获取对应的上下限并判断是否超限
SELECT 
    dw.Batch,
    dw.Parameter,
    dw.MeasurementDate,
    dw.Value,
    vl.ParameterLimitDate AS LimitDate,
    vl.LowerLimit,
    vl.UpperLimit,
    CASE 
        -- 处理无有效限值的情况（比如参数Z）
        WHEN vl.LowerLimit IS NULL OR vl.UpperLimit IS NULL THEN NULL
        WHEN dw.Value < vl.LowerLimit OR dw.Value > vl.UpperLimit THEN 1
        ELSE 0 
    END AS ValueOutsideLimits
FROM DataWithLatestLimit dw
LEFT JOIN ValidLimits vl 
    ON dw.Parameter = vl.Parameter 
    AND dw.LatestLimitDate = vl.ParameterLimitDate
ORDER BY dw.Parameter, dw.Batch, dw.MeasurementDate;

方法二：使用窗口函数直接筛选最新限值 #

这种方法用ROW_NUMBER()窗口函数给每个测量记录匹配到的限值按日期倒序排名，直接取排名第一的最新限值，代码更紧凑。

WITH ValidLimits AS (
    -- 筛选有效限值
    SELECT 
        Parameter,
        ParameterLimitDate,
        LowerLimit,
        UpperLimit
    FROM dbo.Limits
    WHERE 
        NOT (ISNULL(LowerLimit, 0) = 0 AND ISNULL(UpperLimit, 0) = 0)
),
RankedLimitMatches AS (
    -- 给每个测量记录的匹配限值按日期倒序排名，最新的排第1
    SELECT 
        d.Batch,
        d.Parameter,
        d.MeasurementDate,
        d.Value,
        vl.ParameterLimitDate,
        vl.LowerLimit,
        vl.UpperLimit,
        ROW_NUMBER() OVER (
            PARTITION BY d.Batch, d.Parameter, d.MeasurementDate 
            ORDER BY vl.ParameterLimitDate DESC
        ) AS rn
    FROM dbo.Data d
    LEFT JOIN ValidLimits vl 
        ON d.Parameter = vl.Parameter 
        AND vl.ParameterLimitDate <= d.MeasurementDate
)
-- 只保留排名第一的最新限值记录
SELECT 
    Batch,
    Parameter,
    MeasurementDate,
    Value,
    ParameterLimitDate AS LimitDate,
    LowerLimit,
    UpperLimit,
    CASE 
        WHEN LowerLimit IS NULL OR UpperLimit IS NULL THEN NULL
        WHEN Value < LowerLimit OR Value > UpperLimit THEN 1
        ELSE 0 
    END AS ValueOutsideLimits
FROM RankedLimitMatches
WHERE rn = 1
ORDER BY Parameter, Batch, MeasurementDate;

原代码的问题说明 #

原代码的核心问题在于：

1. JOIN方向错误：用LEFT JOIN从Limits到Data，会导致如果有多个限值匹配同一测量记录，就会生成多行；
2. GROUP BY逻辑失效：因为LowerLimit和UpperLimit被包含在GROUP BY中，不同的限值会被分成独立的分组，即使你取了MAX(ParameterLimitDate)，也无法合并这些分组，最终还是会返回多条重复的测量记录。

这两种方案都能完美匹配你的期望输出，包括处理参数Z无有效限值的情况（返回NULL的限值列）。

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