sql查询员工请假详情——请假天数跨月问题

项目中要加个月签到详情页面。需求：就是用户选择月份，查询该月员工签到详情（包括请假）。数据库（mysql）中已有休假表（tt_offwork），表结构如下：

CREATE TABLE `tt_offwork` (`offwork_id` varchar(32) NOT NULL,`create_time` datetime DEFAULT NULL,`day_num` decimal(19,2) DEFAULT NULL COMMENT '请假天数',`from_date` datetime DEFAULT NULL COMMENT '请假起始日期',`instruction` varchar(255) DEFAULT NULL,`is_delete` bit(1) NOT NULL,`offwork_pic` varchar(255) DEFAULT NULL,`offwork_status` varchar(255) DEFAULT NULL COMMENT '请假是否通过',`offwork_type` varchar(255) DEFAULT NULL,`reason` varchar(255) DEFAULT NULL COMMENT '请假理由',`sales_id` varchar(255) DEFAULT NULL COMMENT '人员ID',`to_date` datetime DEFAULT NULL COMMENT '请假结束日期',PRIMARY KEY (`offwork_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一开始没想到请假天数跨月的情况，直接就根据字段”from_date”去匹配用户输入的月份查出记录根据再根据字段”day_num”就可以知道他在这月休假几天。比如用户选择日期”2017-1”，后台sql如下：

SELECTDAY(a.from_date) d, --哪一天开始请假a.day_num,          --请多少天假offwork_type        --请假类型：请假or休假
FROMtt_offwork a
WHEREa.sales_id = '2c90e0825905beaf015905c0092b00de'
AND YEAR (a.from_date) = '2017'
AND MONTH (a.from_date) = '1'
AND a.offwork_status = 'pass'
GROUP BYa.sales_id,DATE_FORMAT(a.from_date, '%Y-%m-%d')
ORDER BYDAY (a.from_date) ASC

后来发现数据有问题，统计休了7天（根据返回的字段”day_num”），但实际这个月只休息了5天，查询sql发现该员工跨月了。

这个员工从2017-01-27 开始，到2017-02-02请假结束，总共休7天。于是乎导致出两个问题：
1. 如果查1月份签到详情，那sql查出休7天，但实际1月份只休5天。
2. 如果查2月份签到详情，那根据字段”from_date”匹配不到2月份，因为该员工的起始请假日期是在1月份，所以这条记录就查不到，导致2月份少了2天的请假天数。
于是，开始分析应该以什么条件去查询sql才能保证数据是对的。员工请假可能出现的所有情况：

方块1-4代表员工可能出现的4种请假的跨度情况，这四种情况都在我们所查的月份范围内。
我们发现：
1. 月初一定大于结束时间，否则就是上个月。
2. 月末一定大于开始时间，否则就是下个月。
按上面两个条件查sql，就可以查出数据，不会出现上面说到的查2月份，但从1月份末开始请假导致2月份查不到数据的情况。
接下来就是如何计算，本月请假天数的问题。GREATEST(月初，开始) - LEAST(月末， 结束)

DATEDIFF(LEAST(a.to_date, STR_TO_DATE('2017-1-31', '%Y-%m-%d')),GREATEST(a.from_date, STR_TO_DATE('2017-1-1', '%Y-%m-%d'))) + 1 AS 'dayNum',

好，那我写得完整sql是

SELECTDAY(IF(a.from_date < '2017-1-1', '2017-1-1', a.from_date)) d,DATEDIFF(LEAST(a.to_date, STR_TO_DATE('2017-1-31', '%Y-%m-%d')),GREATEST(a.from_date, STR_TO_DATE('2017-1-1', '%Y-%m-%d'))) + 1 AS 'dayNum',a.offwork_type
FROMtt_offwork a
WHEREa.sales_id = '2c90e0825905beaf015905c0092b00de'
AND a.from_date <= '2017-1-31' AND a.to_date >= '2017-1-1'
AND a.offwork_status = 'pass'
GROUP BYa.sales_id,DATE_FORMAT(a.from_date, '%Y-%m-%d')
ORDER BYDAY(a.from_date) ASC

思考

需求的关键就是如何做条件判断，把我说的4种情况，全部查出。那从这四种情况找出规律推到出条件有点复杂，这时候就可以利用初中数学知识啦，找出不在我们所查月份范围的集合条件然后取对立面就好了，明显如下图所画的阴影部分，就是不符合的查询条件。这个很容易看出不符合的条件是：月初大于结束时间，月末小于开始时间。于是符合的条件就是取反，月初小于等于结束时间。月末大于等于开始时间。