如何在SQL Server非结构化数据库中关联含重复唯一标识的两张表

嘿，我完全懂你的痛点——同一个设备有好几条借出记录和维护记录，直接按序列号关联只会得到一堆杂乱的匹配结果对吧？别慌，我们可以通过日期范围精准匹配来把每条维护记录和它对应的借出周期绑定起来。

核心思路 #

因为设备序列号重复，所以必须加上时间维度的过滤条件：找到维护发生的时间段（从start maintenance date到finish maintenance date）落在哪一次借出的周期（从equipment check out date到equipment check in date）里。

基础关联SQL（维护完全在借出周期内） #

如果你的维护都是在设备借出期间完成的（维护起止日期都在借出和归还日期之间），用这个查询：

SELECT
    co.[equipment Serial number],
    co.client,
    co.[equipment check out date] AS 借出日期,
    co.[equipment check in date] AS 归还日期,
    m.[start maintenance date] AS 维护开始日期,
    m.[finish maintenance date] AS 维护结束日期,
    m.[maintenance cost] AS 维护费用
FROM
    [设备借出记录表] co
INNER JOIN
    [设备维护记录表] m 
    ON co.[equipment Serial number] = m.[equipment Serial number]
    -- 维护时间段完全包含在借出周期内
    AND m.[start maintenance date] >= co.[equipment check out date]
    AND m.[finish maintenance date] <= co.[equipment check in date];

进阶关联（维护与借出周期有重叠） #

如果存在维护跨借出/归还日期的情况（比如维护开始时设备还在借出中，结束时已经归还），可以用重叠判断的条件：

SELECT
    co.[equipment Serial number],
    co.client,
    co.[equipment check out date] AS 借出日期,
    co.[equipment check in date] AS 归还日期,
    m.[start maintenance date] AS 维护开始日期,
    m.[finish maintenance date] AS 维护结束日期,
    m.[maintenance cost] AS 维护费用
FROM
    [设备借出记录表] co
INNER JOIN
    [设备维护记录表] m 
    ON co.[equipment Serial number] = m.[equipment Serial number]
    -- 判断两个时间段是否有重叠
    AND (
        m.[start maintenance date] BETWEEN co.[equipment check out date] AND co.[equipment check in date]
        OR m.[finish maintenance date] BETWEEN co.[equipment check out date] AND co.[equipment check in date]
        OR co.[equipment check out date] BETWEEN m.[start maintenance date] AND m.[finish maintenance date]
    );

保留所有维护记录（含无对应借出的情况） #

如果想查看所有维护记录，包括那些在设备库存期间进行的维护（没有对应借出记录），可以用LEFT JOIN：

SELECT
    co.[equipment Serial number],
    co.client,
    co.[equipment check out date] AS 借出日期,
    co.[equipment check in date] AS 归还日期,
    m.[start maintenance date] AS 维护开始日期,
    m.[finish maintenance date] AS 维护结束日期,
    m.[maintenance cost] AS 维护费用
FROM
    [设备维护记录表] m
LEFT JOIN
    [设备借出记录表] co 
    ON co.[equipment Serial number] = m.[equipment Serial number]
    AND m.[start maintenance date] >= co.[equipment check out date]
    AND m.[finish maintenance date] <= co.[equipment check in date];

小提示 #

你的日期是YYYYMMDD格式的数字/字符串，在SQL Server里直接比较是有效的，因为这种格式的排序和日期逻辑一致。如果后续改成DATE类型，这些条件依然适用。

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