感谢您的架构。它使处理您的问题变得容易。我对您的架构进行了一些更改以利用auto_increment
CREATE TABLE `emp_leave_summary` ( `id` int(11) NOT NULL AUTO_INCREMENT, `emp_id` int(11) NOT NULL, `leave_from_date` date NOT NULL, `leave_to_date` date NOT NULL, `leave_type` varchar(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `emp_leave_summary` (`emp_id`, `leave_from_date`, `leave_to_date`, `leave_type`) VALUES( 123, '2017-02-01', '2017-02-15', 'Earned Vacation Leave'),( 123, '2017-07-12', '2017-07-26', 'Earned Vacation Leave'),( 456, '2017-03-20', '2017-04-20', 'Earned Vacation Leave'),( 789, '2017-01-15', '2017-02-23', 'Earned Vacation Leave'),( 789, '2017-02-26', '2017-02-27', 'Sick Leave');CREATE TABLE `emp_leave_daywise` ( `id` int(11) NOT NULL AUTO_INCREMENT, `emp_id` int(11) NOT NULL, `leave_date` date NOT NULL, `leave_type` varchar(30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在这里，我在emp_leave_daywise表上添加了唯一约束，因为id整数上的主键不能确保记录不重复。
ALTER TABLE `emp_leave_daywise`ADD UNIQUE KEY `emp_leave_daywise_unique_key` (`emp_id`,`leave_date`,`leave_type`),ADD KEY `emp_id` (`emp_id`),ADD KEY `leave_date` (`leave_date`),ADD KEY `leave_type` (`leave_type`);
emp_leave_summary的唯一键需要一些思考。例如…您是否允许摘要涵盖重叠的日期范围？…
ALTER TABLE `emp_leave_summary`ADD UNIQUE KEY `emp_leave_summary_unique_key` (`emp_id`,`leave_from_date`),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`);
现在使用左连接对现有数据进行数据提取。
insert into emp_leave_daywise ( emp_id, leave_date, leave_type )select `new`.* from ( select summary.emp_id, dates.date_ leave_date, summary.leave_type from emp_leave_summary summary inner join ( select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) 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) dates on dates.date_ >= summary.leave_from_date and dates.date_ <= summary.leave_to_date ) `new` left join emp_leave_daywise old on `new`.emp_id = old.emp_id and `new`.leave_date = old.leave_date and `new`.leave_type = old.leave_type where old.id is null ; select * from emp_leave_daywise order by leave_date, emp_id;根据给定的数据返回104行
id emp_id leave_date leave_type 1 789 2017-01-15 Earned Vacation Leave 2 789 2017-01-16 Earned Vacation Leave 3 789 2017-01-17 Earned Vacation Leave 4 789 2017-01-18 Earned Vacation Leave 5 789 2017-01-19 Earned Vacation Leave 6 789 2017-01-20 Earned Vacation Leave ... 102 123 2017-07-24 Earned Vacation Leave 103 123 2017-07-25 Earned Vacation Leave 104 123 2017-07-26 Earned Vacation Leave
创建日期范围的SQL从这里开始如何在mysql
select查询中获取两个日期之间的日期列表
