基于指定条件实现两表数据关联扩展与插入的技术需求

• 基于lock表对ft表执行数据合并、扩展与插入操作：为ft表添加lock表中的L和time列，同时根据lock表的时间区间匹配，调整ft表的start/end时间范围，并填充对应L和time字段值
• 注意事项：
  1. 列a为主键，不参与计算，插入空值无影响
  2. 需满足过滤条件：b like 'tri%'

原始表数据 #

ft表（周级数据） #

a      b            c           start       end          f        
rohit  tripathi     gj          29-05-22    31-06-22     3
rohit  triple       gj          29-06-22    04-06-22     4
rohit  triwa        gj          05-06-22    11-06-22     7
rohit  triwa        gj          12-06-22    18-06-22     7
rohit  triwa        gj          19-06-22    25-06-22     7
rohit  tritha       gj          26-06-22    30-06-22     5
rohit  triwa        th          26-06-22    02-07-22     2

lock表（含月级/周级数据） #

a       b            c          start        end          L       time
        tri          gj         29-05-22     31-05-22     1       week
        tri          gj         01-06-22     30-06-22     1       month
        tri          gj         01-07-22     07-07-22     1       week

期望结果（更新后的ft表） #

a      b            c           start       end          L          Time      
rohit  tripathi     gj          29-05-22    31-05-22     1          week
rohit  triple       gj          01-06-22    04-06-22     1          month
rohit  triwa        gj          05-06-22    11-06-22     1          month 
rohit  triwa        gj          12-06-22    18-06-22     1          month 
rohit  triwa        gj          19-06-22    25-06-22     1          month
rohit  tritha       gj          26-06-22    30-06-22     1          month
rohit  triwa        th          26-06-22    02-07-22     1          week

实现方案（以MySQL为例） #

1. 为ft表新增列 #

ALTER TABLE ft 
ADD COLUMN L INT, 
ADD COLUMN `time` VARCHAR(10);

2. 匹配lock表更新数据 #

UPDATE ft
JOIN lock 
  ON ft.c = lock.c 
  AND ft.start <= lock.end 
  AND ft.end >= lock.start
SET 
  ft.start = GREATEST(ft.start, lock.start),
  ft.end = LEAST(ft.end, lock.end),
  ft.L = lock.L,
  ft.`time` = lock.`time`
WHERE ft.b LIKE 'tri%';

说明：通过GREATEST和LEAST函数将ft表的时间区间裁剪为与lock表重叠的部分，同时匹配c列关联两张表，最终填充L和time字段值。

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