如何在SQL Server中基于时间区间拆分分数表生成对应SB记录？

现有SQL Server中的SCORE_TBL表包含两种分数类型：M和B。其中M类型的分数值为S（已计分）或SB（奖励计分），要求每个B类型的时间区间[FROM_HRS-TO_HRS]必须对应一条M类型且分数为SB的记录，不能对应S类型的M记录。当前表中记录不符合该规则，需将现有数据转换为指定的目标格式，求实现该转换的SELECT语句。

现有表结构及数据 #

CREATE TABLE SCORE_TBL
(  
    ID int IDENTITY(1,1)  PRIMARY KEY,
    PERSONID_FK int NOT NULL,
    S_TYPE varchar(50) NULL,
    FROM_HRS int NULL,
    TO_HRS int NULL,
    SCORE varchar(50) NULL,
);

INSERT INTO SCORE_TBL(PERSONID_FK,S_TYPE,FROM_HRS,TO_HRS,SCORE)
     VALUES
    (1, 'M' , 0,20, 'S'),
    (1, 'B',6, 8, 'B'),
    (2, 'B',0, 2, 'B'),
    (2, 'M',0,20, 'S'),
    (2, 'B', 10,13, 'B'),
    (2, 'B', 18,20, 'B'),
    (2, 'M', 13,18, 'S'); 

现有数据展示：

目标数据格式 #

以下是实现数据转换的SELECT语句：

WITH AllTimePoints AS (
    -- 收集所有需要分割的时间点：M的起止、B的起止
    SELECT PERSONID_FK, FROM_HRS AS HOUR_POINT FROM SCORE_TBL
    UNION
    SELECT PERSONID_FK, TO_HRS AS HOUR_POINT FROM SCORE_TBL
),
OrderedTimePoints AS (
    -- 按人员和时间点排序，生成相邻时间点对
    SELECT 
        PERSONID_FK, 
        HOUR_POINT,
        LEAD(HOUR_POINT) OVER (PARTITION BY PERSONID_FK ORDER BY HOUR_POINT) AS NEXT_HOUR_POINT
    FROM AllTimePoints
    WHERE HOUR_POINT IS NOT NULL
),
ValidIntervals AS (
    -- 筛选出有效的非空时间区间
    SELECT 
        PERSONID_FK, 
        HOUR_POINT AS FROM_HRS, 
        NEXT_HOUR_POINT AS TO_HRS
    FROM OrderedTimePoints
    WHERE NEXT_HOUR_POINT > HOUR_POINT
),
IntervalTypes AS (
    -- 判断每个区间的M分数类型，同时关联原M记录的覆盖范围
    SELECT 
        vi.PERSONID_FK,
        vi.FROM_HRS,
        vi.TO_HRS,
        CASE WHEN EXISTS (
            SELECT 1 
            FROM SCORE_TBL b
            WHERE b.PERSONID_FK = vi.PERSONID_FK
              AND b.S_TYPE = 'B'
              AND b.FROM_HRS <= vi.FROM_HRS
              AND b.TO_HRS >= vi.TO_HRS
        ) THEN 'SB' ELSE 'S' END AS M_SCORE
    FROM ValidIntervals vi
    JOIN SCORE_TBL m ON vi.PERSONID_FK = m.PERSONID_FK
                    AND m.S_TYPE = 'M'
                    AND vi.FROM_HRS >= m.FROM_HRS
                    AND vi.TO_HRS <= m.TO_HRS
)
-- 合并M类型分割记录与原B类型记录，生成最终结果
SELECT 
    ROW_NUMBER() OVER (ORDER BY PERSONID_FK, FROM_HRS, CASE WHEN S_TYPE = 'B' THEN 1 ELSE 0 END) AS ID,
    PERSONID_FK,
    S_TYPE,
    FROM_HRS,
    TO_HRS,
    SCORE
FROM (
    -- 生成分割后的M类型记录
    SELECT 
        PERSONID_FK,
        'M' AS S_TYPE,
        FROM_HRS,
        TO_HRS,
        M_SCORE AS SCORE
    FROM IntervalTypes
    UNION ALL
    -- 保留原B类型记录
    SELECT 
        PERSONID_FK,
        'B' AS S_TYPE,
        FROM_HRS,
        TO_HRS,
        'B' AS SCORE
    FROM SCORE_TBL
    WHERE S_TYPE = 'B'
) AS CombinedRecords
ORDER BY PERSONID_FK, FROM_HRS, CASE WHEN S_TYPE = 'B' THEN 1 ELSE 0 END;

思路说明 #

1. 收集时间点：通过AllTimePoints收集所有M和B记录的起止时间，为分割大区间做准备。
2. 生成小粒度区间：利用LEAD函数将相邻时间点配对，生成最小单位的有效时间区间。
3. 标记区间分数类型：判断每个小区间是否被B类型的奖励区间完全覆盖，对应标记M分数为SB或S，同时确保区间属于原M记录的覆盖范围。
4. 合并排序：将分割后的M记录与原B记录合并，重新生成自增ID并按人员、时间及类型排序。

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