Staffer表(员工信息)内容如下ID* Name Age Dept1 Jack 25 IT2 Tom 26 IT3 Jessie 27 Sale4 Rose 26 Sale5 Steven 28 Market6 John 22 Market…Worktime表(一月30天全体员工上下班记录,假定30天均需考勤,如员工缺勤本表无相应记录)内容如下ID* InTime OutTime DayNumber*1 8:45 18:17 11 9:00 19:00 2…上表中*表示主键,假定上述数据表字段均不为空,公司规定上班时间为9:00,下班时间18:00,使用Sql语句或存储过程(函数)统计以下数据.迟到、早退、缺勤员工姓名和对应日期各部门员工日均工作时间（部门内员工总工作时间/30/部门人数）
网友回答
【答案】 --迟到SELECT s.name 名字, w.daynumber 日期FROM tbl_staff s, tbl_worktime wWHERE s.id =w.idAND to_date(w.intime,'hh24-mi-ss')>to_date('9:00','hh24-mi-ss') ;--早退SELECT s.name 名字, w.daynumber 日期FROM tbl_staff s, tbl_worktime wWHERE s.id =w.idAND to_date(w.outtime,'hh24-mi-ss')<to_date('18:00','hh24-mi-ss');--缺勤,暂时没想到好的select name,1 daynumber from tbl_staff where id not in(select id from tbl_worktime where daynumber=1)union allselect name,2 daynumber from tbl_staff where id not in(select id from tbl_worktime where daynumber=2)union allselect name,3 daynumber from tbl_staff where id not in(select id from tbl_worktime where daynumber=3)...select name,30 daynumber from tbl_staff where id not in(select id from tbl_worktime where daynumber=30)--部门日均工作时间SELECT s.dept 部门, SUM(ceil((To_date(w.outtime , 'hh24-mi-ss') - To_date(w.intime , 'hh24-mi-ss')) * 24*60))/(30*COUNT(s.dept)) AS 按分钟计算FROM tbl_staff s, tbl_worktime wWHERE s.id=w.idGROUP BY s.dept;