公司规定一周内加班最多12小时,多余的要去掉,去掉的规则是:某天的加班时长超过1小时的才能去,而且依次大致平均的去,(最小单位是1小时):
举例:(table_a)
日期 工号 加班时长
周一 A001 6
周二 A001 2
周三 A001 1
周四 A001 5
周五 A001 7
--------------------
共加班:21小时,超出了9小时 从周一开始依次删减,每天减去1小时(如果加班时间超过1小时),一轮后如果还超过12小时,继续下一轮,直到刚好12小时为止。
最后结果应该是:
日期 工号 加班时长
周一 A001 3 ----减3次
周二 A001 1 ----减1次
周三 A001 1 ----减0次
周四 A001 2 ----减3次
周五 A001 5 ----减2次
--------------------
如果有几千个员工,如何用SQL实现?
举例:(table_a)
日期 工号 加班时长
周一 A001 6
周二 A001 2
周三 A001 1
周四 A001 5
周五 A001 7
--------------------
共加班:21小时,超出了9小时 从周一开始依次删减,每天减去1小时(如果加班时间超过1小时),一轮后如果还超过12小时,继续下一轮,直到刚好12小时为止。
最后结果应该是:
日期 工号 加班时长
周一 A001 3 ----减3次
周二 A001 1 ----减1次
周三 A001 1 ----减0次
周四 A001 2 ----减3次
周五 A001 5 ----减2次
--------------------
如果有几千个员工,如何用SQL实现?
SQL> select *from test;
日期 工号 加班时长 DAY
---- ---- ---------- ----------
周一 A001 6 1
周二 A001 2 2
周三 A001 1 3
周四 A001 5 4
周五 A001 7 5
create or replace procedure proc_test is
n_hours number(5);
i int;
n int;
begin
for r1 in (select 工号 id, sum(加班时长) hours from test group by 工号 having sum(加班时长) > 10) loop n_hours := r1.hours - 12; i := 1;
while n_hours > 0 loop
select count(1) into n from test where 工号=r1.id and day = mod(i-1,5)+1 and 加班时长>1;
if n > 0 then
update test set 加班时长=加班时长-1 where 工号=r1.id and day = mod(i-1,5)+1;
n_hours := n_hours -1 ;
end if;
i := i + 1;
end loop; end loop;end;
/
2:剔除加班为0和1小时的工作日,得到
周一 A001 6
周二 A001 2
周四 A001 5
周五 A001 7
3:9小时除以4,得到每天多出2小时,因此先扣除必须保留的加班一小时,得到加班可分摊时间如下:
周一 A001 5
周二 A001 1
周四 A001 4
周五 A001 64:写过循环算法,每天一次扣1个小时,知道扣满9个小时,得到每天分摊扣除的时间,
然后每天的加班时长就是总加班时间-每天分摊扣除的时间
SQL> begin
2 proc_test;
3 end;
4 /
PL/SQL procedure successfully completed
SQL> select t.* from test t;
日期 工号 加班时长 DAY
---- ---- ---------- ----------
周一 A001 3 1
周二 A001 1 2
周三 A001 1 3
周四 A001 2 4
周五 A001 5 5