获取两个日期之间的日期 [英] Get dates between two dates
问题描述
我有两个这样的表:
表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`);
推荐答案
感谢您的架构.它使处理您的问题变得容易. 我对您的架构进行了一些更改,以利用auto_increment
Thanks for your schema. It makes it easy to work with your question. I changed your schema a little to make use of 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整数上的主键不能确保记录不重复.
Here I added a unique constraint on the emp_leave_daywise table as a primary key on a id integer does not ensure records are not duplicated.
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的唯一键需要一些思考. 例如...您是否允许摘要涵盖重叠的日期范围? ...
The unique key for emp_leave_summary needs some thought. For example ... do you allow summaries covering overlapping date ranges? ...
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`);
现在使用左连接对现有数据进行数据提取.
and now for the data extraction using a left join on existing data.
/*
insert any missing records using a left join on existing records
*/
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 (
/*
get dates to match against
https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query
*/
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行
returns 104 rows based on the data given
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
The date range creation SQL is from here How to get list of dates between two dates in mysql select query
这篇关于获取两个日期之间的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!