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如下：

SELECT DAY(a.from_date) d, --哪一天开始请假 a.day_num, --请多少天假 offwork_type --请假类型：请假or休假 FROM tt_offwork a WHERE a.sales_id = '2c90e0825905beaf015905c0092b00de' AND YEAR (a.from_date) = '2017' AND MONTH (a.from_date) = '1' AND a.offwork_status = 'pass' GROUP BY a.sales_id, DATE_FORMAT(a.from_date, '%Y-%m-%d') ORDER BY DAY (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是

SELECT DAY(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 FROM tt_offwork a WHERE a.sales_id = '2c90e0825905beaf015905c0092b00de' AND a.from_date <= '2017-1-31' AND a.to_date >= '2017-1-1' AND a.offwork_status = 'pass' GROUP BY a.sales_id, DATE_FORMAT(a.from_date, '%Y-%m-%d') ORDER BY DAY(a.from_date) ASC

思考

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