Yeauty
Yeauty
采纳率66.7%
2017-07-26 03:30

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

10
已采纳

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

6条回答

  • u014074697 张大教主 4年前
    //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
    
    点赞 评论 复制链接分享
  • sinat_28505133 一个猴子请来的救兵 4年前

    图片说明

    点赞 1 评论 复制链接分享
  • u014074697 张大教主 4年前
    //以这个为准,少加了空判断
    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
    
    点赞 评论 复制链接分享
  • u014074697 张大教主 4年前
    //如果你的是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
    
    点赞 评论 复制链接分享
  • sinat_28505133 一个猴子请来的救兵 4年前

    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

    点赞 评论 复制链接分享
  • ljheee ljheee 4年前

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

    点赞 评论 复制链接分享