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

huanzai1 2014-01-22 03:39:25

qin_record 考勤记录表
ID 用户ID 时间段考勤时间当前日期
id userid timeslotid time curdate

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

select u.username, r.curdate
,(select time from qin_record where timeslotid=1 and curdate=r.curdate limit 1) as 上午上班
,(select time from qin_record where timeslotid=2 and curdate=r.curdate limit 1) as 上午下班
,(select time from qin_record where timeslotid=3 and curdate=r.curdate limit 1) as 下午上班
,(select time from qin_record where timeslotid=4 and curdate=r.curdate limit 1) as 下午下班
,(select time from qin_record where timeslotid=5 and curdate=r.curdate and 上午上班=null limit 1) as 上午迟到
,(select time from qin_record where timeslotid=6 and curdate=r.curdate and 下午上班=null limit 1) as 下午迟到
,(select time from qin_record where timeslotid=7 and curdate=r.curdate and 上午下班=null limit 1) as 上午早退
,(select time from qin_record where timeslotid=8 and curdate=r.curdate and 下午下班=null limit 1) as 下午早退
,(
(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)
) as 当天缺勤次数
from qin_record r,ums_user u
where r.userid=u.id
group by u.username, r.curdate
order by r.curdate desc

...全文

394 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

_拙计 2014-01-23

打赏
举报

引用 4 楼 huanzai1 的回复:

[quote=引用 2 楼 zlloct 的回复:] 正如楼上所说，你的别名不能在同级查询中使用，你需要再嵌套一层

能能不能写出来啊[/quote]我只是给你个样子又不是叫你照我的写，这一段

((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了

huanzai1 2014-01-22

打赏
举报

引用 5 楼 lu010610 的回复:

[quote=引用 4 楼 huanzai1 的回复:] [quote=引用 2 楼 zlloct 的回复:] 正如楼上所说，你的别名不能在同级查询中使用，你需要再嵌套一层

能能不能写出来啊[/quote]晕，

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

[/quote] 哥，我放进去，提示，unknown column r.curdate in where clause

huanzai1 2014-01-22

打赏
举报

引用 5 楼 lu010610 的回复:

[quote=引用 4 楼 huanzai1 的回复:] [quote=引用 2 楼 zlloct 的回复:] 正如楼上所说，你的别名不能在同级查询中使用，你需要再嵌套一层

能能不能写出来啊[/quote]晕，

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

[/quote] 哥你好强劲，我刚刚才看到，一会套一下看下

鱼缸里的小泥鳅 2014-01-22

打赏
举报

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

,吃力中

_拙计 2014-01-22

打赏
举报

引用 4 楼 huanzai1 的回复:

[quote=引用 2 楼 zlloct 的回复:] 正如楼上所说，你的别名不能在同级查询中使用，你需要再嵌套一层

能能不能写出来啊[/quote]晕，

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

huanzai1 2014-01-22