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

要求：按月找出平均延迟大于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');

//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

'2017-03-20', '2017-03-24'
这些时间都是字符串，就从数据库取出来，先格式化转成日期再比较

select * from (
select datediff(w, plan) as diff, a.* from (
select CASE WHEN a.writes IS NULL THEN date_format(now(),'%Y-%m-%d') ELSE a.writes END as w, a.* from operation a group by w
) a ) a where a.diff > 3

//如果你的是sqlserver，按下面写法，如果是oracle，稍后我再写一个
select a.id 工号,a.name 姓名,sum(a.late_day)/count(*) 每月平均延迟天数  from
(select id,name,year(plan)+'-'+month(plan) month, DATEDIFF(day,write,plan) late_day from operation)a
group by a.id,a.name,a.month
having sum(a.late_day)/count(*)>3

//以这个为准，少加了空判断
select a.id 工号,a.name 姓名,sum(a.late_day)/count(*) 每月平均延迟天数  from
(select id,name,year(plan)+'-'+month(plan) month, DATEDIFF(day,case write when null then getDate() else write end,plan) late_day from operation)a
group by a.id,a.name,a.month
having sum(a.late_day)/count(*)>3