create view record
as
select a.员工id,b.考勤日期,min(b.打卡时间) as 上班 ,max(b.打卡时间) as 下班
from 员工信息表t1 a ,员工考勤表t2 b
where a.员工id=b.员工id
group by a.员工id ,b.考勤日期
create view tb
as
select 员工id,count(case when 上班>8 then 1 else 0 end ) as 迟到 ,count(case when 下班<17 then 1 else 0 end ) as 早退
from record
group by 员工id
select 员工信息表t1.所在部门,tb.员工id,员工信息表t1.员工性名,tb.迟到,tb.早退
from tb,员工信息表t1
where tb.员工id =员工信息表t1.员工id
select 员工id,迟到次数,早退次数 from
(select 员工id, count(*) 迟到次数 from t2 where 上午打卡时间 > 08:00 group by 员工id) a inner join
(select 员工id, count(*) 早退次数 from t2 where 下午打卡时间 < 17:00 group by 员工id) b on a.员工id = b.员工id