SQL问题:mysql查询指定时间段内请假的天数(除去非工作日)
CREATE TABLE `wo_leave_request` (
`id` varchar(32) NOT NULL,
`create_time` datetime DEFAULT NULL,
`total_day` decimal(19,2) DEFAULT NULL COMMENT '请假天数',
`starttime` datetime DEFAULT NULL COMMENT '请假起始日期',
`type_name` varchar(255) DEFAULT NULL COMMENT '请假类型',
`userid` varchar(255) DEFAULT NULL COMMENT '人员ID',
`endtime` datetime DEFAULT NULL COMMENT '请假结束日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `wo_leave_request`
VALUES
(
'张三',
'2017-07-05',
'2017-07-09'
),
(
'张三',
'2017-06-05',
'2017-07-03'
),
(
'张三',
'2017-07-19',
'2017-07-22'
),
(
'王五',
'2017-07-18',
'2017-08-02'
);
CREATE TABLE `attendance_workday` (
`id` varchar(64) NOT NULL,
`iswork` int(11) DEFAULT NULL COMMENT '是否工作标识,0代表非工作日,1代表工作日',
`date` date DEFAULT NULL COMMENT '具体日期',
`week` int(11) DEFAULT NULL COMMENT '星期几的标识,分别有1/2/3/4/5/6/0,代表周一到周日',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考勤后台系统的工作日信息表';
这个表存了是否工作日的信息
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c40097', '1', '2017-06-01', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c40098', '1', '2017-06-02', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c40099', '0', '2017-06-03', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c4009a', '0', '2017-06-04', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c5009b', '1', '2017-06-05', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c5009c', '1', '2017-06-06', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c5009d', '1', '2017-06-07', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c5009e', '1', '2017-06-08', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c5009f', '1', '2017-06-09', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c500a0', '0', '2017-06-10', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c500a1', '0', '2017-06-11', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c500a2', '1', '2017-06-12', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c500a3', '1', '2017-06-13', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a4', '1', '2017-06-14', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a5', '1', '2017-06-15', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a6', '1', '2017-06-16', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a7', '0', '2017-06-17', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a8', '0', '2017-06-18', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600a9', '1', '2017-06-19', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600aa', '1', '2017-06-20', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600ab', '1', '2017-06-21', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c600ac', '1', '2017-06-22', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700ad', '1', '2017-06-23', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700ae', '0', '2017-06-24', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700af', '0', '2017-06-25', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700b0', '1', '2017-06-26', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700b1', '1', '2017-06-27', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700b2', '1', '2017-06-28', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700b3', '1', '2017-06-29', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c700b4', '1', '2017-06-30', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800b5', '0', '2017-07-01', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800b6', '0', '2017-07-02', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800b7', '1', '2017-07-03', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800b8', '1', '2017-07-04', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800b9', '1', '2017-07-05', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800ba', '1', '2017-07-06', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800bb', '1', '2017-07-07', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800bc', '0', '2017-07-08', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c800bd', '0', '2017-07-09', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900be', '1', '2017-07-10', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900bf', '1', '2017-07-11', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c0', '1', '2017-07-12', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c1', '1', '2017-07-13', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c2', '1', '2017-07-14', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c3', '0', '2017-07-15', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c4', '0', '2017-07-16', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c5', '1', '2017-07-17', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47c900c6', '1', '2017-07-18', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00c7', '1', '2017-07-19', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00c8', '1', '2017-07-20', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00c9', '1', '2017-07-21', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00ca', '0', '2017-07-22', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00cb', '0', '2017-07-23', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00cc', '1', '2017-07-24', '1');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00cd', '1', '2017-07-25', '2');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00ce', '1', '2017-07-26', '3');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47ca00cf', '1', '2017-07-27', '4');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47cb00d0', '1', '2017-07-28', '5');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47cb00d1', '0', '2017-07-29', '6');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47cb00d2', '0', '2017-07-30', '0');
INSERT INTO `attendance_workday` VALUES ('ff8080815de0c938015de0ca47cb00d3', '1', '2017-07-31', '1');
指定时间例如 2017-07-01 ~ 2017-07-20 这段时间内每个人的请假天数(除了非工作日)
结果像 张三 6天
王五 1天