关于考勤的几个查询

1. 显示某一天的 上班，下班

数据样式：

sql：

SELECT u.name,s.iname,s.CheckTime,x.iname,x.CheckTime from Users u
LEFT JOIN (SELECT UserId, CheckTime, MIN(CheckTime) mindatetime, iname FROM WX_Sign where iname = '上班' GROUP BY UserId, CheckTime, iname) s on s.userid = u.Account
left join (SELECT UserId, CheckTime, MIN(CheckTime)mindatetime, iname FROM WX_Sign where iname = '下班' GROUP BY UserId, CheckTime, iname ) x on x.userid = u.Account and 
           CONVERT(date, s.CheckTime)= CONVERT(date, x.CheckTime)

where s.CheckTime is not null 

呈现结果：

2. 迟到，早退，人数统计

select u.Name,
sum(case when (ws.iname='上班' and convert(char(5),ws.CheckTime,108) > ws.StdTime)  then 1 else 0 end) '迟到数',
sum(case when (ws.iname='下班' and convert(char(5),ws.CheckTime,108) < ws.StdTime)  then 1 else 0 end) '早退数'
 from WX_Sign ws
left join Users u on ws.UserId = u.Account

GROUP BY u.Name

3. 关于行转列的sql

参考网址：https://www.cnblogs.com/no27/p/6398130.html