Mysql

如何在 MySQL 中使用 INSERT SELECT 將收集的日期插入另一個表?

  • February 5, 2015
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>

試一試 !!!

引用自:https://dba.stackexchange.com/questions/91142