我有两个这样的表:
Table1
emp_leave_summary(id,emp_id,leave_from_date,leave_to_date,leave_type)
Table2
emp_leave_daywise(id,emp_id,leave_date,leave_type)
我想选择emp_id, leave_type
from 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`);