SQL Server无唯一标识关联两表产生重复数据问题求解

核心思路 #

你遇到的重复关联问题根源为：同一设备序列号（serial）、同一测量ID（measurementid）下存在多轮返工的步骤和测量数据，仅用serial + measurementid作为关联条件会产生笛卡尔积，导致重复条目。
可以通过窗口函数为同分组内的记录分配轮次编号，关联时额外匹配轮次即可实现一一对应。

实现逻辑 #

1. 对my_measurement_steps表：按serial、measurementid分组，按步骤记录时间date升序排序，为每一轮返工的步骤生成唯一轮次编号batch_num
2. 对my_measurement_data表：按serial、measurementid分组，按数据记录自增IDid升序排序（自增ID对应采集先后顺序），生成和步骤表规则一致的batch_num
3. 关联时除匹配serial、measurementid外，额外匹配batch_num即可避免交叉关联

完整查询语句 #

WITH steps_with_batch AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY serial, measurementid 
            ORDER BY date ASC
        ) AS batch_num
    FROM my_measurement_steps
),
data_with_batch AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY serial, measurementid 
            ORDER BY id ASC
        ) AS batch_num
    FROM my_measurement_data
)
SELECT 
    s.id AS steps_id,
    s.serial,
    d.id AS data_id,
    d.my_data,
    d.measurementid,
    s.date
FROM steps_with_batch s
INNER JOIN data_with_batch d
    ON s.serial = d.serial
    AND s.measurementid = d.measurementid
    AND s.batch_num = d.batch_num

效果说明 #

• 该方案天然支持多次返工场景，无需修改代码即可适配同一serial + measurementid下2次以上的重复测量需求
• 完全符合期望输出要求：不会出现重复条目，每个data_id仅会匹配到对应轮次的唯一steps_id

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