SQL使用INSERT SELECT插入无重复wk_start、wk_end日期列问题

问题根因 #

代码中使用ON 1=1作为连接条件，本质是交叉连接（笛卡尔积）：两个子查询各自返回N条周数据，连接后会生成N*N条结果，自然出现大量重复数据。

解决方法 #

不需要拆分两个子查询再连接，可以在同一份数字序列生成逻辑中同时计算wk_start和对应的wk_end，一步得到无重复的待插入数据。

正确可运行代码 #

insert into bfs_dw.mpt_calendar (wk_start, wk_end)
select 
  wk_start,
  ADDDATE(wk_start, 6) as wk_end
from (
  select ADDDATE('2022-01-03', 7*(t4*10000 + t3*1000 + t2*100 + t1*10 + t0)) wk_start 
  from
   (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
   (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
   (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
   (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
   (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
) v
where wk_start < '2024-01-03'
order by wk_start

如果确实需要保留双子查询的写法，可以给两个子查询的结果分别生成唯一行号，通过行号相等的关联条件避免笛卡尔积：

insert into bfs_dw.mpt_calendar (wk_start, wk_end)
select 
  ter.wk_start,
  ter1.wk_end
from
  (select 
    wk_start,
    row_number() over(order by wk_start) as rn 
  from (
    select ADDDATE('2022-01-03', 7*(t4*10000 + t3*1000 + t2*100 + t1*10 + t0)) wk_start 
    from
     (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
  ) v
  where wk_start < '2024-01-03') ter
inner join 
  (select 
    wk_end,
    row_number() over(order by wk_end) as rn 
  from (
    select ADDDATE('2022-01-09', 7*(t4*10000 + t3*1000 + t2*100 + t1*10 + t0)) wk_end 
    from
     (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
  ) v
  where wk_end < '2024-01-03') ter1
on ter.rn = ter1.rn
order by ter.wk_start

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