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 )