求助求助，一条SQL统计 考勤情况，下面的当天缺勤次数，统计不出来

Question

能能不能写出来啊晕，select t1.*,
       ((select count(*)
           from qin_record
          where curdate = r.curdate
            and 上午上班 = null
            and 上午迟到 = null) +
       (select count(*)
           from qin_record
          where curdate = r.curdate
            and 下午上班 = null
            and 下午迟到 = null)) 当天缺勤次数
  from (select t.*,
               (select time
                  from qin_record
                 where timeslotid = 5
                   and curdate = t.curdate
                   and 上午上班 = null limit 1) 上午迟到,
               (select time
                  from qin_record
                 where timeslotid = 6
                   and curdate = t.curdate
                   and 下午上班 = null limit 1) 下午迟到,
               (select time
                  from qin_record
                 where timeslotid = 7
                   and curdate = t.curdate
                   and 上午下班 = null limit 1) 上午早退,
               (select time
                  from qin_record
                 where timeslotid = 8
                   and curdate = t.curdate
                   and 下午下班 = null limit 1) 下午早退
          from (select u.username,
                       r.curdate,
                       (select time
                          from qin_record
                         where timeslotid = 1
                           and curdate = r.curdate limit 1) 上午上班,
                       (select time
                          from qin_record
                         where timeslotid = 2
                           and curdate = r.curdate limit 1) 上午下班,
                       (select time
                          from qin_record
                         where timeslotid = 3
                           and curdate = r.curdate limit 1) 下午上班,
                       (select time
                          from qin_record
                         where timeslotid = 4
                           and curdate = r.curdate limit 1) 下午下班
                  from qin_record r, ums_user u
                 where r.userid = u.id
                 group by u.username, r.curdate) t) t1
 order by t1.curdate desc

Answer 1

感觉，ORacle里面很多函数都跟MSSQL的不一样,吃力中

Answer 2

能能不能写出来啊晕，select t1.*,
       ((select count(*)
           from qin_record
          where curdate = r.curdate
            and 上午上班 = null
            and 上午迟到 = null) +
       (select count(*)
           from qin_record
          where curdate = r.curdate
            and 下午上班 = null
            and 下午迟到 = null)) 当天缺勤次数
  from (select t.*,
               (select time
                  from qin_record
                 where timeslotid = 5
                   and curdate = t.curdate
                   and 上午上班 = null limit 1) 上午迟到,
               (select time
                  from qin_record
                 where timeslotid = 6
                   and curdate = t.curdate
                   and 下午上班 = null limit 1) 下午迟到,
               (select time
                  from qin_record
                 where timeslotid = 7
                   and curdate = t.curdate
                   and 上午下班 = null limit 1) 上午早退,
               (select time
                  from qin_record
                 where timeslotid = 8
                   and curdate = t.curdate
                   and 下午下班 = null limit 1) 下午早退
          from (select u.username,
                       r.curdate,
                       (select time
                          from qin_record
                         where timeslotid = 1
                           and curdate = r.curdate limit 1) 上午上班,
                       (select time
                          from qin_record
                         where timeslotid = 2
                           and curdate = r.curdate limit 1) 上午下班,
                       (select time
                          from qin_record
                         where timeslotid = 3
                           and curdate = r.curdate limit 1) 下午上班,
                       (select time
                          from qin_record
                         where timeslotid = 4
                           and curdate = r.curdate limit 1) 下午下班
                  from qin_record r, ums_user u
                 where r.userid = u.id
                 group by u.username, r.curdate) t) t1
 order by t1.curdate desc

哥你好强劲，我刚刚才看到，一会套一下看下

Answer 3

能能不能写出来啊晕，select t1.*,
       ((select count(*)
           from qin_record
          where curdate = r.curdate
            and 上午上班 = null
            and 上午迟到 = null) +
       (select count(*)
           from qin_record
          where curdate = r.curdate
            and 下午上班 = null
            and 下午迟到 = null)) 当天缺勤次数
  from (select t.*,
               (select time
                  from qin_record
                 where timeslotid = 5
                   and curdate = t.curdate
                   and 上午上班 = null limit 1) 上午迟到,
               (select time
                  from qin_record
                 where timeslotid = 6
                   and curdate = t.curdate
                   and 下午上班 = null limit 1) 下午迟到,
               (select time
                  from qin_record
                 where timeslotid = 7
                   and curdate = t.curdate
                   and 上午下班 = null limit 1) 上午早退,
               (select time
                  from qin_record
                 where timeslotid = 8
                   and curdate = t.curdate
                   and 下午下班 = null limit 1) 下午早退
          from (select u.username,
                       r.curdate,
                       (select time
                          from qin_record
                         where timeslotid = 1
                           and curdate = r.curdate limit 1) 上午上班,
                       (select time
                          from qin_record
                         where timeslotid = 2
                           and curdate = r.curdate limit 1) 上午下班,
                       (select time
                          from qin_record
                         where timeslotid = 3
                           and curdate = r.curdate limit 1) 下午上班,
                       (select time
                          from qin_record
                         where timeslotid = 4
                           and curdate = r.curdate limit 1) 下午下班
                  from qin_record r, ums_user u
                 where r.userid = u.id
                 group by u.username, r.curdate) t) t1
 order by t1.curdate desc
哥，我放进去，提示，unknown column r.curdate in where clause

Answer 4

能能不能写出来啊我只是给你个样子 又不是叫你照我的写，这一段((select count(*)            from qin_record           where curdate = r.curdate             and 上午上班 = null            and 上午迟到 = null) +        (select count(*)            from qin_record           where curdate = r.curdate             and 下午上班 = null            and 下午迟到 = null)) 当天缺勤次数 是t1.curdate了