如何使用SQL按循环匹配规则关联多表生成目标增量事实表TableC

该需求完全可以通过SQL实现，核心是通过行号映射+取模运算实现Counter的循环匹配逻辑。

实现逻辑 #

• 首先给TableB的日期行按StartDate升序排序，生成连续的行序号，标识日期的先后顺序
• 统计TableA中每个ID对应的最大Counter值，作为循环取模的除数
• 将每个ID和所有日期行做笛卡尔积，通过公式(日期行号 - 1) % 对应ID的最大Counter值 + 1计算当前日期行对应每个ID要匹配的Counter值，加1是为了对齐Counter从1开始的规则，避免取模得到0的情况
• 用计算得到的目标Counter关联回TableA，拿到对应的Value值，即可得到目标结果表TableC

参考SQL代码 #

WITH b_with_rn AS (
    -- 给TableB的日期按时间顺序生成行号
    SELECT 
        StartDate,
        EndDate,
        ROW_NUMBER() OVER (ORDER BY StartDate ASC) AS b_rn
    FROM TableB
),
a_with_max_counter AS (
    -- 统计每个ID的最大Counter值
    SELECT 
        ID,
        MAX(Counter) AS max_counter
    FROM TableA
    GROUP BY ID
),
id_date_match AS (
    -- 生成ID和日期的匹配关系，计算对应要取的Counter值
    SELECT 
        am.ID,
        bw.StartDate,
        bw.EndDate,
        (bw.b_rn - 1) % am.max_counter + 1 AS target_counter
    FROM a_with_max_counter am
    CROSS JOIN b_with_rn bw
)
-- 关联TableA获取Value，得到最终结果
SELECT 
    idm.ID,
    idm.target_counter AS Counter,
    idm.StartDate,
    idm.EndDate,
    a.Value AS Val
FROM id_date_match idm
INNER JOIN TableA a 
    ON idm.ID = a.ID 
    AND idm.target_counter = a.Counter
ORDER BY idm.StartDate ASC, idm.ID ASC;

适配说明 #

如果使用的是不支持CTE（公共表表达式）的低版本数据库，将上述逻辑改为子查询嵌套即可正常运行，最终输出结果和你提供的TableC完全一致。

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