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

假设这个表的名称叫做 work，来存储打卡的时间信息
select ID,
sum(case when card_time>='%-%-% 08:00:00' and card_time<='%-%-% 08:59:59' then 1 else 0 end ) as '迟到次数',
sum(case when card_time>='%-%-% 09:00:00' or card_time='' then 1 else 0 end ) as '旷工次数',
sum(case when card_time<>'' then 1 else 0 end) as '总的打卡次数'
from work
group by ID
不知道时间这么表示对不对