A. sql考勤统计语句，求助
select name,
sum(case when intime between '8:00' and '8:29' then 1 else 0 end ) as '迟到',
sum(case when intime between '8:30' and '9:00' then 1 else 0 end ) as '缺勤'
from table1
group by name
因为“8：30”是一个公用时间点，不能判别式迟到还是缺勤，
所以迟到我换成“8：29”了
B. SQL语言考勤打卡记录
首先要有一个工厂日历的表，列出所有工作日,至少一个字段：工作日 varchar(10)。
然后这样即可：
select id,
迟到次数=sum(case when timec>'08:00:00' tand timec<'09:00:00' then 1 else 0 end),
旷工次数=sum(case when timec>'09:00:00' or timec is null then 1 else 0 end),
打卡次数=sum(case when timec is null then 0 else 1 end)
from
(
SELECT * FROM 工厂日历 left join
(select id,
datec=convert(varchar(10),card_time,120),
timec=substring(convert(varchar,card_time,120),12,8)
from tablename
) a
on 工作日=DATEC
) b
group by ID
C. 通过输入时间段，查询出勤率，迟到次数，早退次数，旷工次数，用的ssh集成，oracle数据库，怎么写查询方法
--建表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;
D. sql 单表查询语句 求高手帮忙
--总出席的天数
DECLARE @day_count int
select @day_count=count(*) from
(select substring(convert( varchar(10),signtime,120),1,10) a from sign group by substring(convert( varchar(10),signtime,120),1,10)) s;
--考勤表中出现的人
select m1.userid,m1.应考天数,m2.签到次数,m3.签退次数,ISNULL(m4.迟到次数,0) 迟到次数,ISNULL(m5.早退次数,0) 早退次数 from (
select userid,@day_count 应考天数 from sign group by userid) m1 left join (
--每人签到的次数
select count(signid) 签到次数, userid from sign where signflag=1 group by userid ) m2 on m1.userid=m2.userid left join(
--每人签退的次数
select count(signid) 签退次数, userid from sign where signflag=0 group by userid ) m3 on m1.userid=m3.userid left join (
--每人迟到次数
select count(signid) 迟到次数, userid from sign where signflag=1 and convert(varchar(5) ,signtime,114)>'08:00' group by userid ) m4 on m1.userid=m4.userid left join (
--每人早退次数
select count(signid) 早退次数, userid from sign where signflag=0 and convert(varchar(5) ,signtime,114)<'18:00' group by userid) m5 on m1.userid=m5.userid
--把这些语句再用left join 连在一起想要啥就有啥了,都到这步了计算你自己来吧
E. sql语句,查询某个部门的员工考勤异常超过5次的员工,
select
*
from
（select
部门，
员工姓名
,
count(1)
as
'考勤异常数'
from
table
where
部门
=
'X'
and
考勤
=
'异常'
group
by
部门,
员工姓名）
t1
where
t1.考勤异常数
>
5;
---------------------------------------------------------------------------
sql解释：内层语句是统计某个部门（X）考勤有'异常'的员工及异常次数；
外层语句是将异常次数超过5次的员工查询出来。
F. sql sever中我想统计1个人一段时间内的迟到次数
select CONVERT(varchar(12) , 日期字段,112 ),sum(累加字段) from 表名 where 日期字段 between '2013-01-01' and '2013-01-31' group by CONVERT(varchar(12) , 日期字段,112 )