我有两个这样的表:
表格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)
我想emp_id, leave_type
从Table1中选择并插入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
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 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行
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查询中获取两个日期之间的日期列表
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句