求助求助,一条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
...全文
209 点赞 收藏 9
写回复
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日
引用 2 楼 zlloct 的回复:
正如楼上所说,你的别名不能在同级查询中使用,你需要再嵌套一层
能能不能写出来啊
回复 点赞
huanzai1 2014年01月22日
引用 2 楼 zlloct 的回复:
正如楼上所说,你的别名不能在同级查询中使用,你需要再嵌套一层
能不能帮我写出来啊,不是很会啊
回复 点赞
CT_LXL 2014年01月22日
正如楼上所说,你的别名不能在同级查询中使用,你需要再嵌套一层
回复 点赞
_拙计 2014年01月22日
不是你这样写的吧,别名不能作为当前查询的列名,外面再套一层
回复 点赞
发动态
发帖子
Oracle 高级技术
创建于2007-09-28

2927

社区成员

1.8w+

社区内容

Oracle 高级技术相关讨论专区
社区公告
暂无公告