SQL 如何将包含周范围的单行数据拆分为多条单周数据行

假设你的业务表名为week_allocation，提前准备的计数表（tally table）名为tally_weeks，计数表中存储周数值的字段名为week_num，可直接使用如下关联查询实现拆分：

SELECT
  wa.ID,
  wa.Year,
  tw.week_num AS StartWeek,
  tw.week_num AS EndWeek,
  wa.AllocationPercent
FROM week_allocation wa
INNER JOIN tally_weeks tw 
  ON tw.week_num BETWEEN wa.StartWeek AND wa.EndWeek;

方案说明 #

• 关联逻辑仅保留计数表中落在当前业务行周范围内的周数，每匹配到一个周数就生成一条对应单周行
• 原业务行的ID、Year、AllocationPercent字段完全保留，新行的StartWeek和EndWeek统一取计数表匹配到的周数值，符合要求的输出规则
• 针对你给出的示例数据，上述查询运行后会输出5条记录：ID为5的行对应2021年周34、周35共2条，ID为6的行对应2021年周1、周2、周3共3条，和预期输出完全一致

注意事项 #

• 请提前确认计数表tally_weeks的week_num取值范围覆盖你业务表中所有StartWeek到EndWeek的数值区间，避免出现漏拆分的情况
• 如果需要新生成每行的唯一主键ID，可根据你使用的数据库类型在SELECT字段中添加对应函数：MySQL可加UUID() AS NewID，SQL Server可加NEWID() AS NewID，PostgreSQL可加gen_random_uuid() AS NewID

内容的提问来源于stack exchange，提问作者Ville Alijärvi