SQL实现如下:--创建员工基本信息表 create table Staffer as select 1 id, 'Jack' name, 25 age, 'IT' dept from dual union all select 2 id, 'Tom' name, 26 age, 'IT' dept from dual union all select 3 id, 'Jessie' name, 27 age, 'Sale' dept from dual union all select 4 id, 'Rose' name, 26 age, 'Sale' dept from dual union all select 5 id, 'Steven' name, 28 age, 'Market' dept from dual union all select 6 id, 'John' name, 22 age, 'Market' dept from dual--创建考勤表 create table Worktime as select 1 id, '8:45' intime, '18:17' outtime, 1 daynumber from dual union all select 1 id, '9:00' intime, '19:00' outtime, 2 daynumber from dual--题1:迟到、早退、缺勤员工姓名和日期,用union all关联查询 --1.查询迟到和早退的员工姓名和日期 select a.name,b.daynumber from Staffer a,Worktime b where a.id = b.id and ((case when a.intime > '9:00' then 1 else 0 end)+(case when a.outtime < '18:00' then 1 else 0 end))>0 union all --2.查询缺勤的员工姓名和日期 select t1.name,t1.daynumber from(select a.id, a.name,b.daynumber from Staffer a,(select 1+rownum-1 daynumber from dual connect by rownum <= 30) b ) t1 left join (select distinct id,daynumber from Worktime) t2 on t2.id = t1.id where t2.id is null;--题2:各部门员工日均工作时间: select a.dept,round(sum(b.hours)/30/count(*),2) avg_work from Staffer a left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime from Worktime ) group by a.dept;
而且 这个b.hours 中的b 并没有定义
你试试这个:select a.dept,round(sum(b.hours)/30/count(*),2) avg_work from Staffer a left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime from Worktime )b on a.id = b.id group by a.dept;
-- Create table create table T_STAFFER ( id INTEGER, name VARCHAR2(50), age INTEGER, dept VARCHAR2(20) ); -- Create table create table T_WORKTIME ( id INTEGER, intime VARCHAR2(20), outtime VARCHAR2(20), daynumber INTEGER );prompt Importing table t_staffer... set feedback off set define off insert into t_staffer (ID, NAME, AGE, DEPT) values (7369, 'SMITH', 29, '20');insert into t_staffer (ID, NAME, AGE, DEPT) values (7499, 'ALLEN', 26, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7521, 'WARD', 25, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7566, 'JONES', 25, '20');insert into t_staffer (ID, NAME, AGE, DEPT) values (7654, 'MARTIN', 34, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7698, 'BLAKE', 33, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7782, 'CLARK', 38, '10');insert into t_staffer (ID, NAME, AGE, DEPT) values (7788, 'SCOTT', 32, '20');insert into t_staffer (ID, NAME, AGE, DEPT) values (7839, 'KING', 29, '10');insert into t_staffer (ID, NAME, AGE, DEPT) values (7844, 'TURNER', 21, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7876, 'ADAMS', 38, '20');insert into t_staffer (ID, NAME, AGE, DEPT) values (7900, 'JAMES', 21, '30');insert into t_staffer (ID, NAME, AGE, DEPT) values (7902, 'FORD', 36, '20');insert into t_staffer (ID, NAME, AGE, DEPT) values (7934, 'MILLER', 33, '10');prompt Done. insert into t_worktime select id, lpad(trunc(dbms_random.value(8, 10)), 2, '0') || ':' || lpad(trunc(dbms_random.value(0, 59)), 2, '0') intime,
lpad(trunc(dbms_random.value(17, 21)), 2, '0') || ':' || lpad(trunc(dbms_random.value(0, 59)), 2, '0') outtime, daynum from t_staffer, (select level daynum from dual connect by level <= 30) order by id, daynum;commit;-- 随机删除几条数据, 制造缺勤 delete t_worktime where rowid in (select rid from (select rowid rid, daynumber, trunc(dbms_random.value(1, 50)) random from t_worktime t) where random = daynumber); commit;-- 先看看每个员工这个月的出勤情况 select id, count(*) cnt from t_worktime group by id order by cnt;-- 第一题的答案 -- 这种实现方式有一点不好的, 当员工数量比较多的时候, 例如 富士康500W员工, 这里就会有性能问题, -- 因为这里使用笛卡尔积枚举出每个员工出勤30天的数据 with v_staff_days as (select /* 枚举出每个员工30天的考勤 */ s.id, daynumber, s.dept from (select level daynumber from dual connect by level <= 30) days cross join (select id, dept from t_staffer) s) --select * from v_staff_days order by id, daynumber select t1.id, t1.daynumber, t1.dept, t2.intime, t2.outtime, (case when t2.id is null then '缺勤' when t2.intime > '09:00' and t2.outtime < '18:00' then '迟到、早退' when t2.intime > '09:00' then '迟到' when t2.outtime < '18:00' then '早退' end) reason from v_staff_days t1 left join t_worktime t2 on (t1.id = t2.id and t1.daynumber = t2.daynumber) where ((t2.id is null) --缺勤 or (t2.intime > '09:00') --迟到 or (t2.outtime < '18:00') --早退 ) -- and t1.id = 7900 -- 可加上这个过滤条件看看缺勤天数是否对的上 order by t1.id, t1.daynumber; ----------------------------------------用存储过程来实现的话, 效率会高很多---------------------------------------- with v_base as (select t.*, lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber, (case when t.intime > '09:00' and t.outtime < '18:00' then 3 when t.intime > '09:00' then 1 when t.outtime < '18:00' then 2 else 0 end) status from t_worktime t) select a.*, (case when next_daynumber - daynumber > 1 then 1 else 0 end) lost_range_ from v_base a; 详见 存储过程实现思路.png -- 搞一个表来存储异常考勤的数据 create table t_incorrect_log( staffid int, -- 员工id daynumber int, -- 考勤日期 status int, -- 是否迟到、早退 状态 time1 int, --迟到时间(单位:分钟) time2 int, --早退时间(单位:分钟) lost int -- 是否缺勤 1 是 0 否 );create procedure proc_analysis_incorrect_kq as type tbl_type is table of t_incorrect_log%rowtype index by pls_integer; tbl tbl_type; -- 用来存放考勤异常记录 i pls_integer; -- 计数的 v_final_date_prefix varchar2(20) := to_char(sysdate, 'yyyyMMdd'); v_final_intime date := to_date(v_final_date_prefix || ' 09:00', 'yyyyMMdd hh24:mi'); v_final_outtime date := to_date(v_final_date_prefix || ' 18:00', 'yyyyMMdd hh24:mi'); begin-- 这里最好是分为两个for循环进行处理, 分别对 主SQL再添加过滤条件 status <> 0 表示迟到的 lost_range_ <> 0 表示缺勤的 -- 这里我偷懒就只写一个了 for r in (with v_base as (select t.*, lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber, (case when t.intime > '09:00' and t.outtime < '18:00' then 3 when t.intime > '09:00' then 1 when t.outtime < '18:00' then 2 else 0 end) status from t_worktime t) select a.*, (case when next_daynumber - daynumber > 1 then 1 else 0 end) lost_range_ from v_base a) loop
-- 判断迟到 if r.status <> 0 then i := tbl.count + 1; --要+1 tbl(i).staffid := r.id; tbl(i).daynumber := r.daynumber; tbl(i).status := r.status;
-- 批量插入 if i >= 50000 then forall x in 1 .. tbl.count insert into t_incorrect_log (staffid, daynumber, status, time1, time2, lost) values (tbl(x).staffid, tbl(x).daynumber, tbl(x).status, tbl(x).time1, tbl(x).time2, tbl(x).lost);
commit; tbl.delete(); -- 一定要清空tbl end if;
end loop; -- 最后一批数据可能没有达到50000条, 别漏了操作 forall x in 1 .. tbl.count insert into t_incorrect_log (staffid, daynumber, status, time1, time2, lost) values (tbl(x).staffid, tbl(x).daynumber, tbl(x).status, tbl(x).time1, tbl(x).time2, tbl(x).lost); commit;end; /select * from t_incorrect_log; truncate table t_incorrect_log; -- 每次测试都先删掉记录表的数据 -- 现在可在 t_incorrect_log 进行更多的分析了, 如看谁是迟到3巨头。。是否超出每个月限定的迟到次数、迟到时间等等 -- 校验是否正确: -- 运行完存储过程后, 查看 t_incorrect_log 表的数据量, 看和第一条SQL查询出来的数据量是否一致。 -- 也可单独看某个员工的出勤,来和 t_incorrect_log 表的数据进行比对 -- 第二题的答案:每个员工每个月工作总时长 select b.dept, sum(a.day_worktime) / count(distinct a.id) staff_cnt / 30 from (select x.id, (to_date(x.outtime, 'hh24:mi') - to_date(x.intime, 'hh24:mi')) * 24 day_worktime from t_worktime x) a inner join t_staffer b on (a.id = b.id) group by b.dept;
create table Staffer as
select 1 id, 'Jack' name, 25 age, 'IT' dept from dual union all
select 2 id, 'Tom' name, 26 age, 'IT' dept from dual union all
select 3 id, 'Jessie' name, 27 age, 'Sale' dept from dual union all
select 4 id, 'Rose' name, 26 age, 'Sale' dept from dual union all
select 5 id, 'Steven' name, 28 age, 'Market' dept from dual union all
select 6 id, 'John' name, 22 age, 'Market' dept from dual--创建考勤表
create table Worktime as
select 1 id, '8:45' intime, '18:17' outtime, 1 daynumber from dual union all
select 1 id, '9:00' intime, '19:00' outtime, 2 daynumber from dual--题1:迟到、早退、缺勤员工姓名和日期,用union all关联查询
--1.查询迟到和早退的员工姓名和日期
select a.name,b.daynumber
from Staffer a,Worktime b
where a.id = b.id
and ((case when a.intime > '9:00' then 1 else 0 end)+(case when a.outtime < '18:00' then 1 else 0 end))>0
union all
--2.查询缺勤的员工姓名和日期
select t1.name,t1.daynumber
from(select a.id, a.name,b.daynumber
from Staffer a,(select 1+rownum-1 daynumber from dual connect by rownum <= 30) b
) t1
left join (select distinct id,daynumber from Worktime) t2 on t2.id = t1.id
where t2.id is null;--题2:各部门员工日均工作时间:
select a.dept,round(sum(b.hours)/30/count(*),2) avg_work
from Staffer a
left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours
from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime
from Worktime
)
group by a.dept;
from Staffer a
left join(select id, round(to_date(outtime,'yyyymmdd hh24:mi:ss') - to_date(intime,'yyyymmdd hh24:mi:ss') ,2) hours
from(select id,to_char(trunc(sysdate),'yyyymmdd')||' '||intime as intime,to_char(trunc(sysdate),'yyyymmdd')||' '||outtime as outtime
from Worktime
)b
on a.id = b.id
group by a.dept;
-- Create table
create table T_STAFFER
(
id INTEGER,
name VARCHAR2(50),
age INTEGER,
dept VARCHAR2(20)
);
-- Create table
create table T_WORKTIME
(
id INTEGER,
intime VARCHAR2(20),
outtime VARCHAR2(20),
daynumber INTEGER
);prompt Importing table t_staffer...
set feedback off
set define off
insert into t_staffer (ID, NAME, AGE, DEPT)
values (7369, 'SMITH', 29, '20');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7499, 'ALLEN', 26, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7521, 'WARD', 25, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7566, 'JONES', 25, '20');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7654, 'MARTIN', 34, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7698, 'BLAKE', 33, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7782, 'CLARK', 38, '10');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7788, 'SCOTT', 32, '20');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7839, 'KING', 29, '10');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7844, 'TURNER', 21, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7876, 'ADAMS', 38, '20');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7900, 'JAMES', 21, '30');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7902, 'FORD', 36, '20');insert into t_staffer (ID, NAME, AGE, DEPT)
values (7934, 'MILLER', 33, '10');prompt Done.
insert into t_worktime
select id,
lpad(trunc(dbms_random.value(8, 10)), 2, '0') || ':' ||
lpad(trunc(dbms_random.value(0, 59)), 2, '0') intime,
lpad(trunc(dbms_random.value(17, 21)), 2, '0') || ':' ||
lpad(trunc(dbms_random.value(0, 59)), 2, '0') outtime,
daynum
from t_staffer, (select level daynum from dual connect by level <= 30)
order by id, daynum;commit;-- 随机删除几条数据, 制造缺勤
delete t_worktime
where rowid in (select rid
from (select rowid rid,
daynumber,
trunc(dbms_random.value(1, 50)) random
from t_worktime t)
where random = daynumber);
commit;-- 先看看每个员工这个月的出勤情况
select id, count(*) cnt from t_worktime group by id order by cnt;-- 第一题的答案
-- 这种实现方式有一点不好的, 当员工数量比较多的时候, 例如 富士康500W员工, 这里就会有性能问题,
-- 因为这里使用笛卡尔积枚举出每个员工出勤30天的数据
with v_staff_days as
(select /* 枚举出每个员工30天的考勤 */
s.id, daynumber, s.dept
from (select level daynumber from dual connect by level <= 30) days
cross join (select id, dept from t_staffer) s)
--select * from v_staff_days order by id, daynumber
select t1.id,
t1.daynumber,
t1.dept,
t2.intime,
t2.outtime,
(case
when t2.id is null then
'缺勤'
when t2.intime > '09:00' and t2.outtime < '18:00' then
'迟到、早退'
when t2.intime > '09:00' then
'迟到'
when t2.outtime < '18:00' then
'早退'
end) reason
from v_staff_days t1
left join t_worktime t2
on (t1.id = t2.id and t1.daynumber = t2.daynumber)
where ((t2.id is null) --缺勤
or (t2.intime > '09:00') --迟到
or (t2.outtime < '18:00') --早退
)
-- and t1.id = 7900 -- 可加上这个过滤条件看看缺勤天数是否对的上
order by t1.id, t1.daynumber;
----------------------------------------用存储过程来实现的话, 效率会高很多----------------------------------------
with v_base as
(select t.*,
lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber,
(case
when t.intime > '09:00' and t.outtime < '18:00' then
3
when t.intime > '09:00' then
1
when t.outtime < '18:00' then
2
else
0
end) status
from t_worktime t)
select a.*,
(case
when next_daynumber - daynumber > 1 then
1
else
0
end) lost_range_
from v_base a;
详见 存储过程实现思路.png
-- 搞一个表来存储异常考勤的数据
create table t_incorrect_log(
staffid int, -- 员工id
daynumber int, -- 考勤日期
status int, -- 是否迟到、早退 状态
time1 int, --迟到时间(单位:分钟)
time2 int, --早退时间(单位:分钟)
lost int -- 是否缺勤 1 是 0 否
);create procedure proc_analysis_incorrect_kq as
type tbl_type is table of t_incorrect_log%rowtype index by pls_integer;
tbl tbl_type; -- 用来存放考勤异常记录 i pls_integer; -- 计数的 v_final_date_prefix varchar2(20) := to_char(sysdate, 'yyyyMMdd');
v_final_intime date := to_date(v_final_date_prefix || ' 09:00',
'yyyyMMdd hh24:mi');
v_final_outtime date := to_date(v_final_date_prefix || ' 18:00',
'yyyyMMdd hh24:mi');
begin-- 这里最好是分为两个for循环进行处理, 分别对 主SQL再添加过滤条件 status <> 0 表示迟到的 lost_range_ <> 0 表示缺勤的
-- 这里我偷懒就只写一个了
for r in (with v_base as
(select t.*,
lead(daynumber, 1, daynumber) over(partition by t.id order by t.daynumber) next_daynumber,
(case
when t.intime > '09:00' and t.outtime < '18:00' then
3
when t.intime > '09:00' then
1
when t.outtime < '18:00' then
2
else
0
end) status
from t_worktime t)
select a.*,
(case
when next_daynumber - daynumber > 1 then
1
else
0
end) lost_range_
from v_base a) loop
-- 判断迟到
if r.status <> 0 then
i := tbl.count + 1; --要+1
tbl(i).staffid := r.id;
tbl(i).daynumber := r.daynumber;
tbl(i).status := r.status;
tbl(i).time1 := (to_date(v_final_date_prefix || ' ' || r.intime,
'yyyyMMdd hh24:mi') - v_final_intime) * 24 * 60; --迟到时间(单位:分钟)
tbl(i).time2 := (v_final_outtime -
to_date(v_final_date_prefix || ' ' || r.outtime,
'yyyyMMdd hh24:mi')) * 24 * 60; --早退时间(单位:分钟)
if r.status = 2 then
-- 只早退的不要记录迟到时间,否则会变成负数
tbl(i).time1 := null;
elsif r.status = 1 then
-- 只迟到的不记录早退时间,否则会变成负数
tbl(i).time2 := null;
end if;
tbl(i).lost := 0;
end if;
-- 判断缺勤
if r.lost_range_ = 1 then
-- 找出缺勤的日期
for j in r.daynumber + 1 .. r.next_daynumber - 1 loop
dbms_output.put_line(r.daynumber || '~' || r.next_daynumber ||
' ---> ' || j);
i := tbl.count + 1; --要+1
tbl(i).staffid := r.id;
tbl(i).daynumber := j; -- 缺勤日
tbl(i).status := null;
tbl(i).time1 := null; --迟到时间(单位:分钟)
tbl(i).time2 := null; --早退时间(单位:分钟)
tbl(i).lost := 1;
end loop;
end if;
-- 批量插入
if i >= 50000 then
forall x in 1 .. tbl.count
insert into t_incorrect_log
(staffid, daynumber, status, time1, time2, lost)
values
(tbl(x).staffid,
tbl(x).daynumber,
tbl(x).status,
tbl(x).time1,
tbl(x).time2,
tbl(x).lost);
commit;
tbl.delete(); -- 一定要清空tbl
end if;
end loop; -- 最后一批数据可能没有达到50000条, 别漏了操作
forall x in 1 .. tbl.count
insert into t_incorrect_log
(staffid, daynumber, status, time1, time2, lost)
values
(tbl(x).staffid,
tbl(x).daynumber,
tbl(x).status,
tbl(x).time1,
tbl(x).time2,
tbl(x).lost); commit;end;
/select * from t_incorrect_log;
truncate table t_incorrect_log; -- 每次测试都先删掉记录表的数据
-- 现在可在 t_incorrect_log 进行更多的分析了, 如看谁是迟到3巨头。。是否超出每个月限定的迟到次数、迟到时间等等
-- 校验是否正确:
-- 运行完存储过程后, 查看 t_incorrect_log 表的数据量, 看和第一条SQL查询出来的数据量是否一致。
-- 也可单独看某个员工的出勤,来和 t_incorrect_log 表的数据进行比对
-- 第二题的答案:每个员工每个月工作总时长
select b.dept, sum(a.day_worktime) / count(distinct a.id) staff_cnt / 30
from (select x.id,
(to_date(x.outtime, 'hh24:mi') - to_date(x.intime, 'hh24:mi')) * 24 day_worktime
from t_worktime x) a
inner join t_staffer b
on (a.id = b.id)
group by b.dept;