一个关于考勤统计的sql研究

在这里，我们要做一个简单的员工考勤记录查询系统的后台数据库。业务需求如下所示：

     1.统计每天来的最早、来的最晚、走的最早、走得最晚的人的姓名

          1.1 统计每天来得最早的人的姓名和打卡时间

            步骤1：从考勤信息表中查询出每天来得最早的人的上班打卡时间和人员编号

     解决这个问题的时候本来考虑的是在考勤信息记录表中按照日期对考勤信息进行分组，然后取每组中上班时间（att_work_datatime）的最小值，但是后来几经折腾发现group by只能实现分组，而order by只能实现组外排序，因此这个方法只能放弃。再三考虑了一下，可以在分组之前先对表中att_work_datatime列中的所有值进行升序排序后生成一个临时表，然后对这个临时表中的att_work_datatime按照日期再分组，这样对att_work_datatime列按照日期group by之后取的就是每天上班打卡最早的人，我们从attendance_info_table（考勤信息表）表中查询出出每天上班时间最早的人的编号、上班时间和下班时间，sql语句如下： 

         select tmp.att_work_datatime,tmp.after_work_datatime,tmp.emp_id 

          from

          (select id,att_work_datatime,after_work_datatime,emp_id

                from attendance_info_table

                order by att_work_datatime) as tmp

           group by Date(att_work_datatime)

          执行查询结果如下：

          

          结果并不如愿，发现多了一行null值，才发现分组的时候mysql默认将null分为一组，这样就多了一组null数据。这样我们只需要在排序之前过滤掉  打卡时间为null的数据行，sql语句如下：

           select tmp.att_work_datatime,tmp.after_work_datatime,tmp.emp_id

            from

           (select id,att_work_datatime,after_work_datatime,emp_id

                from attendance_info_table

                 where att_work_datatime is not null

order by att_work_datatime) as tmp

     group by Date(att_work_datatime)

     查询出来的结果如下图所示：

     

     步骤2：从员工表和考勤信息表中联结查询出每天来得最早的人的姓名上班打卡时间    

      这样，还没有满足需求，我们要打印的是每天来得最早的人的姓名和上班打卡时间，由于员工的信息在另外一张表employee_info_table中放着，这样我们需要用到多表联结查询，根据雇员编号进行等值联结查询，sql语句如下所示：

     select em.emp_name,tmp.att_work_datatime
     from employee_info_table as em ,
     (select id,att_work_datatime,after_work_datatime,emp_id
          from attendance_info_table
          where att_work_datatime is not null
          order by att_work_datatime )
     as tmp
     where em.id=tmp.emp_id
     group by Date(att_work_datatime)

 

select eit.*, ait.att_work_datatime

from attendance_info_table ait, employee_info_table eit

where ait.emp_id = eit.id and ait.att_work_datatime in

     (select min(att_work_datatime)

       from attendance_info_table

        where att_work_datatime is not null

          group by Date(att_work_datatime))

order by ait.att_work_datatime asc;

     查询出来的结果如下图所示：

     

     OK，大功告成。在这里要说明的是，为了缩短sql语句并且为了在一条sql语句中多次使用相同的表，上面的查询中我们使用em 作为员工信息表employee_info_table 的表别名，使用tmp作为排序后生成的临时表的表别名。

     1.2 统计每天来得最晚的人的姓名和打卡时间

     步骤和2.1中统计每天来的最早的人的方法相同，唯一不同的是分组之前先对表中att_work_datatime列中的所有值进行降序排序，sql语句如下：

     select em.emp_name,tmp.att_work_datatime 

     from employee_info_table as em ,
     (select id,att_work_datatime,after_work_datatime,emp_id
          from attendance_info_table
          where att_work_datatime is not null
          order by att_work_datatime desc)
     as tmp
     where em.id=tmp.emp_id
     group by Date(att_work_datatime)

 

select eit.*, ait.att_work_datatime 

from attendance_info_table ait, employee_info_table eit

where ait.emp_id = eit.id and ait.att_work_datatime in

     (select max(att_work_datatime) 

     from attendance_info_table 

     where att_work_datatime is not null 

     group by Date(att_work_datatime))

order by ait.att_work_datatime asc;

     执行查询的结果如下：

     

 

      1.3 统计每天走得最早的人的姓名和打卡时间

             步骤和2.1中统计每天来的最早的人的方法相同，唯一不同的是对表中列中after_work_datatime的所有值进行升   序排序，并将查询的列由att_work_datatime改为after_work_datatime，sql语句如下：

          select em.emp_name,tmp.after_work_datatime 

          from employee_info_table as em ,
          (select id,att_work_datatime,after_work_datatime,emp_id
               from attendance_info_table
               where after_work_datatime is not null
               order by after_work_datatime)
          as tmp
          where em.id=tmp.emp_id
          group by Date(after_work_datatime)

 

select eit.*, ait.after_work_datatime 

from attendance_info_table ait, employee_info_table eit

where ait.emp_id = eit.id and ait.after_work_datatime in 

     (select min(after_work_datatime)

     from attendance_info_table

     where after_work_datatime is not null

     group by Date(after_work_datatime))

order by ait.after_work_datatime asc;

     查询结果如下：

          

 

      1.4 统计每天走得最晚的人的姓名和打卡时间

           步骤和2.2中统计每天来的最晚的人的方法相同，唯一不同的是对表中列中after_work_datatime的所有值进行降序排序，并将查询的列由att_work_datatime改为after_work_datatime，sql语句如下：

     select em.emp_name,tmp.after_work_datatime 

     from employee_info_table as em ,
     (select id,att_work_datatime,after_work_datatime,emp_id
          from attendance_info_table
          where after_work_datatime is not null
          order by after_work_datatime desc)
     as tmp
     where em.id=tmp.emp_id
     group by Date(after_work_datatime)

 

select eit.*, ait.after_work_datatime

from attendance_info_table ait, employee_info_table eit

where ait.emp_id = eit.id and ait.after_work_datatime in

     (select max(after_work_datatime)

     from attendance_info_table

     where after_work_datatime is not null

     group by Date(after_work_datatime))

order by ait.after_work_datatime asc;

     查询结果如下：

     

  

    2.统计每天工作时间最长、工作时间最短的人的姓名

      2.1统计每天工作时间最长的人的姓名

          步骤1：从考勤信息表中查询出每天工作时间最长的人的编号和工作时长

          解决这个问题，我们需要建立在问题1解决方法的基础上，我们先取出考勤信息表中上班打卡时间att_work_datatime、下班打卡时          间after_work_datatime、上下班打卡时间之差作为一天的工作时长att_time以及员工编号emp_id生成一个临时表tmp并将打卡时间为null的数据过滤掉，然后对tmp表中的att_time进行降序排 序然后根据日期进行分组，这样我们就可以从attendance_info_table（考勤信息表）表中查询出每天工作时间最长的人的编号和此人的工作时长。为了计算两个时间差，我们使用mysql自带的函数timediff(time1,time2)来计算time1-time2的时长。sql语句如下：

               select tmp.att_time,tmp.emp_id

               from

               (select id,att_work_datatime,after_work_datatime,timediff(after_work_datatime,att_work_datatime) as att_time,emp_id

                    from attendance_info_table

                    where att_work_datatime is not null and after_work_datatime is not null

                   order by att_time desc) as tmp

                 group by Date(att_work_datatime)

                  查询出的结果如下图所示：

                   

               步骤2：从考勤信息表和员工表中利用等值联结查询出每天工作时间最长的人的姓名和工作时长

               我们根据雇员编号进行等值联结查询出每天工作时间最长的人的姓名和工作时长，sql语句如下所示：

select eit.*,tmp.att_time, tmp.att_work_datatime

from employee_info_table eit,

     (select id,att_work_datatime,timediff(after_work_datatime,att_work_datatime) as att_time,emp_id

     from attendance_info_table

     where att_work_datatime is not null and after_work_datatime is not null) as tmp

where eit.id=tmp.emp_id and tmp.att_time in

(select max(timediff(after_work_datatime,att_work_datatime)) as att_time   

        from attendance_info_table

        where att_work_datatime is not null and after_work_datatime is not null

     group by date(att_work_datatime))

group by date(tmp.att_work_datatime)

order by att_work_datatime;

执行出的结果如下图所示：

            

      2.2统计每天工作时间最短的人的姓名

select eit.*,tmp.att_time, tmp.att_work_datatime

from employee_info_table eit,

     (select id,att_work_datatime,timediff(after_work_datatime,att_work_datatime) as att_time,emp_id

     from attendance_info_table

     where att_work_datatime is not null and after_work_datatime is not null) as tmp

where eit.id=tmp.emp_id and tmp.att_time in

(select min(timediff(after_work_datatime,att_work_datatime)) as att_time    

        from attendance_info_table

        where att_work_datatime is not null and after_work_datatime is not null

     group by date(att_work_datatime))

group by date(tmp.att_work_datatime)

order by att_work_datatime;

          执行结果如下所示：

          

     3.统计每天迟到的人数、早退的人数

     3.1统计每天迟到的人数

select date(att_work_datatime) as date,count(*)  as late_nums

from attendance_info_table

where timediff(time(att_work_datatime),'09:30:59') > 0 and att_work_datatime is not null

group by date(att_work_datatime)

     执行结果如下图所示：

     

      3.2统计每天早退的人数

select date(after_work_datatime) as date,count(*)  as leave_early_nums

from attendance_info_table

where after_work_datatime is not null

     and timediff(time(after_work_datatime),'18:00:00')<0

     or timediff(after_work_datatime,att_work_datatime)<'08:00:00'

group by date(after_work_datatime)      

执行结果如下图所示：

     

     4.统计每个月迟到的人按迟到次数降序排序

select eit.*, count(*) as late_nums

from attendance_info_table as ait,employee_info_table as eit

where ait.att_work_datatime is not null

     and timediff(time(ait.att_work_datatime),'09:30:59') > 0

     and ait.emp_id = eit.id   

group by emp_id

order by  late_nums desc;

          执行结果如下：

     

     5.统计出迟到的人并按姓名按升序排序，打印出迟到的时间

select eit.*,timediff(time(ait.att_work_datatime),'09:30:59') as lately_times,date(ait.att_work_datatime) as lately_date

from attendance_info_table as ait,employee_info_table as eit

where ait.att_work_datatime is not null

     and timediff(time(ait.att_work_datatime),'09:30:59') > 0

     and eit.id=ait.emp_id

order by eit.emp_name asc;

执行结果如下：

     6.公司规定：每迟到一次扣10块钱，每分钟扣1块钱，计算出每天迟到的人扣的钱和公司一天因为迟到扣的钱的总数

     6.1计算出每天迟到的人扣的钱

select eit.*,timediff(time(ait.att_work_datatime),'09:30:59') as lately_times,date(ait.att_work_datatime) as lately_date,(10+1*(TIME_TO_SEC(timediff(time(ait.att_work_datatime),'09:30:59')))/60) as '罚金(元)'
from attendance_info_table as ait,employee_info_table as eit
where ait.att_work_datatime is not null
     and timediff(time(ait.att_work_datatime),'09:30:59') > 0
     and eit.id=ait.emp_id
order by eit.emp_name asc

执行结果如下：

6.1计算出公司每天因为迟到所扣的钱

select tmp.lately_date,sum(tmp.fadefor)  as '总罚金(元)'
from
     (select eit.*,timediff(time(ait.att_work_datatime),'09:30:59') as lately_times,date(ait.att_work_datatime) as lately_date,(10+1*(TIME_TO_SEC(timediff(time(ait.att_work_datatime),'09:30:59')))/60) as fadefor
     from attendance_info_table as ait,employee_info_table as eit
     where ait.att_work_datatime is not null
          and timediff(time(ait.att_work_datatime),'09:30:59') > 0
          and eit.id=ait.emp_id
     order by eit.emp_name asc) as tmp
group by  tmp.lately_date

执行结果如下：

     7.统计出每个月每个人因为迟到扣多少钱，按扣的钱数降序排序列出名单

     步骤一：统计出每个人每天迟到的时间并计算每个人每天的罚金

               方法同6.1

        步骤二：根据人员编号进行分组，统计每个人每个月所扣的钱,并排序

select tmp.id,tmp.emp_name,sum(tmp.fadefor) as 'total_fadefor' from
     (select eit.*,(10+1*(TIME_TO_SEC(timediff(time(ait.att_work_datatime),'09:30:59')))/60) as 'fadefor'
     from attendance_info_table as ait,employee_info_table as eit
     where ait.att_work_datatime is not null
          and timediff(time(ait.att_work_datatime),'09:30:59') > 0
          and eit.id=ait.emp_id) as tmp
group by tmp.id
order by total_fadefor desc;    

          

           查询结果如下：

          

   8.列举出既没有迟到也没有早退记录的人的名单

     步骤一：统计出每个人每个月正常出勤的天数

select eit.*,count(*) as normal_nums

from attendance_info_table as ait,employee_info_table as eit

where ait.att_work_datatime is not null

     and ait.after_work_datatime is not null

     and timediff(time(ait.att_work_datatime),'09:30:59') < 0

     and timediff(after_work_datatime,att_work_datatime)>'08:00:00'

     and ait.emp_id = eit.id    

group by ait.emp_id

 

步骤2：查询出出勤次数大于指定天数的人的名单

select tmp.id,tmp.emp_name from
(select eit.*,count(*) as normal_nums
from attendance_info_table as ait,employee_info_table as eit
where ait.att_work_datatime is not null
     and ait.after_work_datatime is not null
     and timediff(time(ait.att_work_datatime),'09:30:59') < 0
     and timediff(after_work_datatime,att_work_datatime)>'08:00:00'
     and ait.emp_id = eit.id
group by ait.emp_id
)as tmp where tmp.normal_nums>=21 

步骤三：通过获取一个月的天数，查询出一个月每天都正常出勤的人的名单

select tmp.id,tmp.emp_name from
(select eit.*,count(*) as normal_nums
from attendance_info_table as ait,employee_info_table as eit
where ait.att_work_datatime is not null
     and ait.after_work_datatime is not null
     and timediff(time(ait.att_work_datatime),'09:30:59') < 0
     and timediff(after_work_datatime,att_work_datatime)>'08:00:00'
     and ait.emp_id = eit.id
group by ait.emp_id
)as tmp
where tmp.normal_nums>=
(select count(*)
     from
     (select date(att_work_datatime)  as date
     from attendance_info_table
     where att_work_datatime is not null
     group by date(att_work_datatime)) as tmp)

执行结果如下：