mysql - 获取两个日期之间的日期

Question

我有两个这样的表:

表1

emp_leave_summary(id,emp_id,leave_from_date,leave_to_date,leave_type)

表2

emp_leave_daywise(id,emp_id,leave_date,leave_type)

我想从 Table1 中选择 emp_id, leave_type 并插入到 Table2。

例如:在表 1 中我有这个

id,emp_id,leave_from_date,leave_to_date,leave_type
 1, 12345,2017-07-01     ,2017-07-03   ,Sick Leave

在表2中，我想要这个

id,emp_id,leave_date,leave_type
 1,12345,2017-07-01,Sick Leave
 2,12345,2017-07-02,Sick Leave
 3,12345,2017-07-03,Sick Leave

带有示例数据的表结构

    CREATE TABLE `emp_leave_summary` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `leave_from_date` date NOT NULL,
  `leave_to_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `emp_leave_summary` (`id`, `emp_id`, `leave_from_date`, `leave_to_date`, `leave_type`) VALUES
(1, 123, '2017-02-01', '2017-02-15', 'Earned Vacation Leave'),
(2, 123, '2017-07-12', '2017-07-26', 'Earned Vacation Leave'),
(3, 456, '2017-03-20', '2017-04-20', 'Earned Vacation Leave'),
(4, 789, '2017-01-15', '2017-02-23', 'Earned Vacation Leave'),
(5, 789, '2017-02-26', '2017-02-27', 'Sick Leave');

CREATE TABLE `emp_leave_daywise` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `leave_date` date NOT NULL,
  `leave_type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


ALTER TABLE `emp_leave_daywise`
ADD PRIMARY KEY (`id`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_date` (`leave_date`),
ADD KEY `leave_type` (`leave_type`);


ALTER TABLE `emp_leave_summary`
ADD PRIMARY KEY (`id`),
ADD KEY `emp_id` (`emp_id`),
ADD KEY `leave_type` (`leave_type`),
ADD KEY `leave_from_date` (`leave_from_date`),
ADD KEY `leave_to_date` (`leave_to_date`);

Answer 1

尝试:

    select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i 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 i 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 i 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 i 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 i 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 '2012-02-10' and '2012-02-15'

-对于 future 近 300 年的日期范围。

来自 How to get list of dates between two dates in mysql select query