根据指定类型和日期查找对应类型下激活状态且权重最高的项目

看起来你已经摸到了问题的核心——这确实是个结合状态区间生成和同一类型内权重优先级合并的间隙与孤岛问题。你的思路方向完全正确：先为每个Item生成完整的状态区间，再合并同类型的激活区间并选出对应时间段的最高权重Item。我来一步步帮你梳理出可行的解决方案：

第一步：为每个Item生成完整的激活状态区间 #

首先我们要给每个Item整理出它的每一段激活状态的时间区间。这里要注意处理两种情况：如果是Item的最后一条状态记录且为激活，区间的结束时间设为NULL（表示持续到未来）；如果有后续状态变更，就用下一条记录的日期作为当前区间的结束。

对应的SQL CTE如下：

WITH ItemStatusIntervals AS (
    SELECT
        i.Id AS ItemId,
        i.Type,
        i.Weight,
        sh.StatusDate AS FromDate,
        -- 取下一条状态变更的日期作为当前区间的结束时间
        LEAD(sh.StatusDate) OVER (PARTITION BY sh.ItemId ORDER BY sh.StatusDate) AS ToDate,
        sh.IsActive
    FROM StatusHistory sh
    INNER JOIN Items i ON sh.ItemId = i.Id
),
-- 过滤出仅激活状态的区间，并处理最后一条记录的开放区间
ActiveItemIntervals AS (
    SELECT
        ItemId,
        Type,
        Weight,
        FromDate,
        -- 仅保留激活状态的区间，最后一条激活记录的ToDate设为NULL
        CASE WHEN IsActive = 1 THEN ToDate END AS ToDate
    FROM ItemStatusIntervals
    WHERE IsActive = 1
    AND (ToDate IS NULL OR ToDate > FromDate) -- 排除异常的时间倒序记录
)

这段代码会生成所有Item处于激活状态的完整时间段，比如Item1的区间是2025-01-01到2025-02-01，Item3的区间是2025-02-01到NULL。

第二步：合并同类型区间，选出各时间段的最高权重Item #

这是解决问题的核心环节：我们需要把同一Type的所有激活区间整合，找出每个连续时间段内权重最高的激活Item，合并成Type级别的有效区间。

继续扩展CTE：

,
-- 收集同一Type下所有激活区间的起始、结束时间点，生成所有关键时间节点
TypeTimePoints AS (
    SELECT Type, FromDate AS TimePoint FROM ActiveItemIntervals
    UNION
    SELECT Type, ToDate AS TimePoint FROM ActiveItemIntervals WHERE ToDate IS NOT NULL
),
-- 将Type的关键时间点排序，切割成最小的连续时间片段
TypeTimeRanges AS (
    SELECT
        Type,
        TimePoint AS RangeStart,
        LEAD(TimePoint) OVER (PARTITION BY Type ORDER BY TimePoint) AS RangeEnd
    FROM TypeTimePoints
),
-- 为每个Type的时间片段，选出该时间段内激活的最高权重Item
TypeTopItemRanges AS (
    SELECT
        ttr.Type,
        ttr.RangeStart AS FromDate,
        ttr.RangeEnd AS ToDate,
        -- 选出当前片段内激活Item中权重最高的那个的ID
        FIRST_VALUE(aii.ItemId) OVER (
            PARTITION BY ttr.Type, ttr.RangeStart
            ORDER BY aii.Weight DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS TopItemId
    FROM TypeTimeRanges ttr
    LEFT JOIN ActiveItemIntervals aii
        ON ttr.Type = aii.Type
        -- 检查时间片段与Item激活区间是否重叠
        AND (aii.FromDate < ttr.RangeEnd OR ttr.RangeEnd IS NULL)
        AND (aii.ToDate > ttr.RangeStart OR aii.ToDate IS NULL)
    WHERE ttr.RangeEnd IS NOT NULL OR EXISTS (
        -- 处理开放时间片段（即当前/未来时间）
        SELECT 1 FROM ActiveItemIntervals aii2
        WHERE aii2.Type = ttr.Type AND aii2.ToDate IS NULL
    )
),
-- 合并连续的、同一最高权重Item的区间（解决间隙与孤岛问题）
MergedTypeRanges AS (
    SELECT
        Type,
        MIN(FromDate) AS FromDate,
        MAX(ToDate) AS ToDate,
        TopItemId,
        -- 用分组标记连续的相同最高权重区间
        ROW_NUMBER() OVER (PARTITION BY Type, TopItemId ORDER BY FromDate)
        - ROW_NUMBER() OVER (PARTITION BY Type ORDER BY FromDate) AS GroupId
    FROM TypeTopItemRanges
    WHERE TopItemId IS NOT NULL -- 仅保留有有效Item的区间
    GROUP BY Type, TopItemId,
        ROW_NUMBER() OVER (PARTITION BY Type, TopItemId ORDER BY FromDate)
        - ROW_NUMBER() OVER (PARTITION BY Type ORDER BY FromDate)
)

第三步：匹配任意给定的Type和Date #

有了上面生成的MergedTypeRanges区间表，我们就可以快速匹配任意给定的Type和Date，得到对应的最高权重ItemId：

-- 示例：匹配你给出的目标日期列表
SELECT
    td.Type,
    td.TargetDate,
    -- 无匹配区间时返回NULL
    mr.TopItemId AS [Matching Item Id]
FROM (
    SELECT 'T1' AS Type, '2025-01-02' AS TargetDate UNION ALL
    SELECT 'T1' AS Type, '2025-01-20' AS TargetDate UNION ALL
    SELECT 'T1' AS Type, '2025-02-15' AS TargetDate UNION ALL
    SELECT 'T2' AS Type, '2025-01-02' AS TargetDate UNION ALL
    SELECT 'T2' AS Type, '2025-02-15' AS TargetDate UNION ALL
    SELECT 'T3' AS Type, '2025-01-02' AS TargetDate UNION ALL
    SELECT 'T3' AS Type, '2025-02-15' AS TargetDate
) td
LEFT JOIN MergedTypeRanges mr
    ON td.Type = mr.Type
    AND mr.FromDate <= td.TargetDate
    AND (mr.ToDate IS NULL OR mr.ToDate > td.TargetDate)
ORDER BY td.Type, td.TargetDate;

这段查询会完全输出你预期的结果，包括T2在2025-02-15时返回NULL的情况。

关键说明 #

1. 我们先确保每个Item的激活区间准确，再通过收集所有关键时间点把同类型的时间切成最小连续片段，避免遗漏任何权重变化的节点
2. 用窗口函数在每个时间片段内选出最高权重的激活Item，再合并连续的相同结果区间，避免生成过多细碎记录
3. 对于没有任何激活Item的Type或时间段，自动返回NULL，完全符合你的需求

内容来源于stack exchange