Mysql
如何在 MySQL 中使用 INSERT SELECT 將收集的日期插入另一個表?
insert into rfdate (date) select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date 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 where selected_date between '2015-02-01' and '2015-02-28' and dayname(selected_date) in ('Monday', 'Tuesday')
上面的程式碼不起作用。
下面的程式碼會收集所需的日期,而不管您在哪個數據庫中,因為它只是日期。即使不使用顯示列的表格,您也可以嘗試。
select * from (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date 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 selected_date between '2015-02-01' and '2015-02-28' and dayname(selected_date) in ('Monday', 'Tuesday');
提議的查詢
insert into rfdate (date) select selected_date from ( select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date 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 ) A where selected_date between '2015-02-01' and '2015-02-28' and dayname(selected_date) in ('Monday', 'Tuesday');
證明它有效
mysql> DROP DATABASE jeffvalerio; Query OK, 1 row affected (0.02 sec) mysql> CREATE DATABASE jeffvalerio; Query OK, 1 row affected (0.00 sec) mysql> USE jeffvalerio Database changed mysql> CREATE TABLE rfdate -> ( -> id int not null auto_increment, -> date date, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.05 sec) mysql> insert into rfdate (date) -> select selected_date from -> ( -> select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date -> 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 -> ) A where selected_date between '2015-02-01' and '2015-02-28' -> and dayname(selected_date) in ('Monday', 'Tuesday'); Query OK, 8 rows affected (0.09 sec) Records: 8 Duplicates: 0 Warnings: 0
這是表格的內容
mysql> select * from rfdate; +----+------------+ | id | date | +----+------------+ | 1 | 2015-02-02 | | 2 | 2015-02-03 | | 3 | 2015-02-09 | | 4 | 2015-02-10 | | 5 | 2015-02-16 | | 6 | 2015-02-17 | | 7 | 2015-02-23 | | 8 | 2015-02-24 | +----+------------+ 8 rows in set (0.00 sec) mysql>
試一試 !!!