通过输入时间段，查询出勤率，迟到次数，早退次数，旷工次数，用的ssh集成，oracle数据库，怎么写查询方法

发布网友 发布时间：2022-05-29 05:06

我来回答

共3个回答

热心网友 时间：2023-10-02 17:58

--建表SQL
Create Table mhl.manualsign --考勤信息表
(ms_id int,--非空 种子，自增1 签卡Id

user_id Varchar(50) not null,--自增1 签卡Id

ms_time date not null,-- 非空 签卡时间

ms_desc Varchar(200) not null,-- 非空 签卡备注

ms_tag int not null-- 非空 签卡标记 （1,上班打卡，0，下班打卡）
);

create table mhl.tbl_worktime --上下班时间表

(wt_id int not null,-- 非空 种子，自增1 工作时间Id

wt_uptime Varchar(50) not null,-- 非空 上班时间

wt_downtime Varchar(50) not null-- 非空 下班时间
);
-- mhl.manualsign插入数据
truncate table mhl.manualsign;
insert into mhl.manualsign
select 1,'001', to_date('2012-01-01 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 2,'001', to_date('2012-01-01 17:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 3,'001', to_date('2012-01-02 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 4,'001', to_date('2012-01-02 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 5,'001', to_date('2012-01-03 09:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 6,'001', to_date('2012-01-03 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 7,'001', to_date('2012-01-05 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 8,'001', to_date('2012-01-05 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al
union
select 9,'001', to_date('2012-01-07 08:04:54','yyyy-mm-dd hh24:mi:ss'),'001','1' from al
union
select 10,'001', to_date('2012-01-07 18:04:54','yyyy-mm-dd hh24:mi:ss'),'001','0' from al;

insert into mhl.manualsign
select 11,'002', to_date('2012-01-01 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 12,'002', to_date('2012-01-01 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 13,'002', to_date('2012-01-02 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 14,'002', to_date('2012-01-02 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 15,'002', to_date('2012-01-03 09:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 16,'002', to_date('2012-01-03 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 17,'002', to_date('2012-01-05 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 18,'002', to_date('2012-01-05 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al
union
select 19,'002', to_date('2012-01-07 08:04:54','yyyy-mm-dd hh24:mi:ss'),'002','1' from al
union
select 20,'002', to_date('2012-01-07 18:04:54','yyyy-mm-dd hh24:mi:ss'),'002','0' from al;
--mhl.tbl_worktime插入数据
truncate table mhl.tbl_worktime;
insert into mhl.tbl_worktime
select 1,'2012-01-01 09:00:00','2012-01-01 18:00:00' from al;

----通过输入时间段，查询出勤率，迟到次数，早退次数，旷工次数
--设置入参：开始时间为：2012-01-01 结束时间为 2012-01-07
select a.user_id,
(select Count(1) / 2
From mhl.manualsign c
where c.user_id = a.user_id
and c.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and c.ms_time <= To_Date('2012-01-07', 'yyyy-mm-dd')) /
(select To_Date('2012-01-07', 'yyyy-mm-dd') -
To_Date('2012-01-01', 'yyyy-mm-dd')
from al) 出勤率,
(Select Count(1)
From mhl.manualsign d
where a.user_id = d.user_id
and d.ms_tag = '1'
and To_char(d.ms_time, 'hh24:mi:ss') > To_char('09:00:00')
and d.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and d.ms_time <= To_Date('2012-01-07', 'yyyy-mm-dd')
and d.user_id = a.user_id) 迟到次数,
(Select Count(1)
From mhl.manualsign d
where a.user_id = d.user_id
and d.ms_tag = '0'
and to_char(d.ms_time, 'hh24:mi:ss') < to_char('18:00:00')
and d.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and d.ms_time <= To_Date('2012-02-01', 'yyyy-mm-dd')
and d.user_id = a.user_id) 早退次数,
round(round(1 + To_Date('2012-01-07', 'yyyy-mm-dd') -
To_Date('2012-01-01', 'yyyy-mm-dd')) -
(select count(1) / 2
from mhl.manualsign f
where f.user_id = '001'
and f.ms_time >= To_Date('2012-01-01', 'yyyy-mm-dd')
and f.ms_time <= 1 + To_Date('2012-01-07', 'yyyy-mm-dd'))) 旷工次数
from mhl.manualsign a
where a.ms_time between To_Date('2012-01-01', 'yyyy-mm-dd') and /*a.ms_time <=*/
To_Date('2012-01-07', 'yyyy-mm-dd')
group by a.user_id;

热心网友 时间：2023-10-02 17:59

可以分开写
--迟到次数
select count(1) from manualsign a where a.ms_time >to_time(时间值) and a.ms_tag =1 and a.user_id='员工的ID' and tbl_worktime between startTime and endTime
--早退次数
select count(1) from manualsign a where a.ms_time <to_time(时间值) and a.ms_tag =0 and a.user_id='员工的ID' and tbl_worktime between startTime and endTime
其他的自己照着写就行了
想要连成一个sql也可以，可以把这几个当子查询

热心网友 时间：2023-10-02 17:59

select
(select count(*) from manualsign where ms_time>开始时间 and ms_time < 结束时间 and
wt_uptime > 9：00)/(select count(*) from manualsign where ms_time>开始时间 and ms_time < 结束时间) 出勤率,
(select count(*) from manualsign group by user_id where ms_time>开始时间 and ms_time < 结束时间 and wt_uptime < 9：00 ) 迟到次数,
(select count(*) from manualsign group by user_id where ms_time>开始时间 and ms_time < 结束时间 and wt_uptime is null) 旷工次数
from al

希望对你有帮助