SQL-INSERTSELECT兩列沒有重復

我正在嘗試做兩個子查詢來用兩列填充我的表。wk_start和wk_end。目標是用特定范圍的date.

我試過交叉連接，但仍然有與這個相同的重復項

這是我的代碼

insert into bfs_dw.mpt_calendar (wk_start, wk_end)

select 

ter.wk_start,
ter1.wk_end

from

(select * 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 ) ter

inner JOIN 

(select * 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'
order by wk_end ) ter1 ON 1 = 1

輸出顯示如下。插入后有很多重復項。

我想要做的是這個預期的結果，一旦插入就沒有重復：

注意：這兩個子查詢運行良好。它的輸出是這里發布的最后一張圖片。

uj5u.com熱心網友回復：

使用第一個子查詢獲取wk_start，并添加 6 天以獲取wk_end

insert into bfs_dw.mpt_calendar (wk_start, wk_end)

select wk_start, wk_start   INTERVAL 6 DAY 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'

標籤：mysql sql 插入 子查询 插入-选择

上一篇：Mysql搜索兩張表

下一篇：使用awk從檔案或EV中獲取環境變數以將其用作EV