如何解决日期范围重叠时查询保单限额出现重复记录的问题？

你的核心问题是业务规则优先级未明确，单纯依赖日期重叠关联会导致符合多个规则的保单（比如003）同时匹配到LimitID1和2，产生重复。以下是两种直接有效的解决方式：

方式一：直接用明确业务规则关联（推荐） #

既然需求有明确的匹配规则，完全不需要依赖模糊的日期重叠逻辑，直接将规则写入关联条件，从根源避免重复：

SELECT 
    p.[Policy #],
    p.[Policy Effective Date],
    p.[Policy termination date],
    p.Year,
    l.LimitID,
    l.Limit
FROM Policy p
JOIN [Limit] l ON 
    -- 规则1：2018-2020年的保单（001/002/003）匹配LimitID 1
    (p.Year IN (2018, 2019, 2020) AND l.LimitID = 1)
    -- 规则2：生效日期晚于2021-01-01的保单匹配LimitID 2
    OR (p.[Policy Effective Date] > '2021-01-01' AND l.LimitID = 2)

这种方式下，每个保单只会匹配符合自身规则的唯一限额记录，不会出现重复。

方式二：保留日期重叠逻辑，通过排序去重 #

如果因业务限制必须保留日期重叠的关联逻辑，可以用ROW_NUMBER()给匹配结果按规则优先级排序，只取最符合需求的第一条：

WITH RankedMatches AS (
    SELECT 
        p.[Policy #],
        p.[Policy Effective Date],
        p.[Policy termination date],
        p.Year,
        l.LimitID,
        l.Limit,
        -- 按业务规则优先级排序：规则1优先于规则2
        ROW_NUMBER() OVER (
            PARTITION BY p.[Policy #] 
            ORDER BY 
                CASE 
                    WHEN p.Year IN (2018,2019,2020) AND l.LimitID = 1 THEN 1
                    WHEN p.[Policy Effective Date] > '2021-01-01' AND l.LimitID = 2 THEN 2
                    ELSE 3 
                END
        ) AS MatchRank
    FROM Policy p
    JOIN [Limit] l ON 
        (l.[Effective Date] < p.[Policy termination date]
         AND l.[Termination Date] > p.[Policy Effective Date])
        OR 
        (l.[Effective Date] = p.[Policy Effective Date] 
         AND l.[Termination Date] = CONVERT(datetime, '01/01/3000', 102))
)
SELECT 
    [Policy #],
    [Policy Effective Date],
    [Policy termination date],
    Year,
    LimitID,
    Limit
FROM RankedMatches
WHERE MatchRank = 1;

原查询重复原因说明 #

原查询仅判断日期范围是否重叠，但保单003的有效期（2020-10-01至2021-10-01）同时和LimitID1（2018-10-01至2021-10-01）、LimitID2（2018-10-01至3000-01-01）的日期范围重叠，因此会返回两条记录。解决的核心是把业务规则优先级加入匹配逻辑，而不是只看日期重叠。

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