sql语句，关于时间的复杂查询

Question

要求：按月找出平均延迟大于3天的人员信息
同一个人员一天填写有多条则取最早的一条，如果有一天未填写时间，则取系统当前时间，不含小时

数据插入如下：

---

-- Table structure for operation

---

DROP TABLE IF EXISTS operation;
CREATE TABLE operation (
id int(11) DEFAULT NULL COMMENT '工号',
name varchar(255) DEFAULT NULL COMMENT '姓名',
content varchar(255) DEFAULT NULL COMMENT '工作内容',
plan date DEFAULT NULL COMMENT '计划填写时间',
write date DEFAULT NULL COMMENT '实际填写时间',
working int(11) DEFAULT NULL COMMENT '工时数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

-- Records of operation

---

INSERT INTO operation VALUES ('1', '春哥', '填写：春哥第一次回头', '2017-03-20', '2017-03-23', '1');
INSERT INTO operation VALUES ('1', '春哥', '填写：春哥第二次回头', '2017-03-20', '2017-03-23', '2');
INSERT INTO operation VALUES ('1', '春哥', '填写：春哥第三次回头', '2017-03-20', '2017-03-24', '5');
INSERT INTO operation VALUES ('1', '春哥', '填写：春哥第四次回头', '2017-03-24', '2017-03-30', '8');
INSERT INTO operation VALUES ('2', '曽哥', '填写：曾哥第一次笑了', '2017-03-20', '2017-03-30', '8');
INSERT INTO operation VALUES ('2', '曽哥', '填写：曽哥第二次笑了', '2017-03-25', '2017-03-28', '8');
INSERT INTO operation VALUES ('3', '犀利哥', '填写：犀利哥打了个喷嚏', '2017-03-20', null, null);
INSERT INTO operation VALUES ('3', '犀利哥', '填写：犀利哥又打了个喷嚏', '2017-03-20', null, null);
INSERT INTO operation VALUES ('4', '撒盐哥', '填写：撒盐哥睡了个觉', '2017-03-25', '2017-03-28', '8');

Answer 1

//mysql数据库写法
select a.id 工号,a.name 姓名,sum(a.late_day)/count(*) 每月平均延迟天数  from
(select id,name,date_format(plan,'%Y-%m') month, datediff(case write when null then current_date else write end,plan) late_day from operation)a
group by a.id,a.name,a.month
having sum(a.late_day)/count(*)>3