mysql 考勤表 数据库考勤表设计

前言：漂亮的人事小姐姐找我帮忙弄考勤：由于人员考勤和门禁一起，打卡记录太多，打卡机只能导出一个打卡Excel总表，不容易人工筛选。

Excel表的格式是这样的：（这里101代替真实人名）

实现目标：

8：30上班，5：30下班，可以导出工作日来公司人员迟到早退和没来公司人员的缺勤情况表，这两个结果表。
也可以导出每人每天最早打卡最晚打卡记录表。

这个表需要导入MySQL数据库进行筛选。迟到早退好判断，其实这里最难判断的是全天缺勤，因为打卡机没有任何记录。
需要自动生成人员表（把不打卡人去掉），上班日期表（把非工作日去掉），打卡表（每人每天最早最晚打卡）3个表联查的。

1.先把Excel表格转下格式（分列）

转完后格式这样了

 2.把表格导入MySQL数据库

3.因为人员在变动，每回给的打卡表人员不会一模一样，需要自动生成人员表（把不打卡人去掉），上班日期表（把非工作日去掉），打卡表（每人每天最早最晚打卡）3个表联查的。-- -- 删除不用打卡记录人员

-- -- 删除不用打卡记录人员
DELETE FROM `考勤明细` WHERE `姓名` IN ('boss','张总','钟总','吴总');
-- 
-- --创建上班日期表动态：超过一半人打卡加班算工作日：80是全体人员每天打卡次数，超过80代表上班日期，正常一天打卡110左右，周末单独来公司开门打卡的人不会超过80次打卡
DROP TABLE if exists d;
CREATE TABLE d (SELECT `日期时间` FROM `考勤明细` WHERE (f5 <'08:30:00') OR (f5 >'17:30:00')  GROUP BY `日期时间` HAVING COUNT(`姓名`) > 80);
-- SELECT * FROM d;
-- 
-- -- 创建人员临时表格
DROP TABLE if exists u;
CREATE  TABLE u (SELECT  `姓名` FROM `考勤明细` GROUP BY `姓名`);
-- SELECT * FROM u;
-- 
-- -- 创建考勤明细表
DROP TABLE if exists k;
CREATE TABLE k (SELECT * ,min(`f5`) as "最早打卡", max(`f5`) as "最晚打卡" FROM `考勤明细`   GROUP BY `姓名`,`日期时间`  ORDER BY `日期时间`,`姓名`); 
--  SELECT * FROM k ORDER BY `姓名`,`日期时间`;

-- 早上没打卡-- -- 晚上没打卡，这应该链接上班日期d表
SELECT `姓名`,`日期时间`,`f4` as '星期',`最早打卡`,`最晚打卡`,'迟到' FROM k WHERE `最早打卡` >'08:30:00' AND f4 !='星期日' AND f4 !='星期六'
UNION
SELECT `姓名`,`日期时间`,`f4` as '星期',`最早打卡`,`最晚打卡`,'早退' FROM k WHERE `最晚打卡` <'17:30:00' AND f4 !='星期日' AND f4 !='星期六' ORDER BY `姓名`,`日期时间`;

第2种写法：
(SELECT k.`姓名`,k.`日期时间`,k.`f4` as '星期',k.`最早打卡`,k.`最晚打卡`,'迟到' FROM k RIGHT JOIN d on k.`日期时间` = d.`日期时间` WHERE k.`最早打卡` >'08:30:00')
 UNION 
(SELECT k.`姓名`,k.`日期时间`,k.`f4` as '星期',k.`最早打卡`,k.`最晚打卡`,'早退' FROM k RIGHT JOIN d on k.`日期时间` = d.`日期时间` WHERE `最晚打卡` <'17:30:00' ) 
ORDER BY `姓名`,`日期时间`;


-- 
-- 早上也没打卡，晚上也没打卡，但是来了（这个不用了，上面能查出来）
-- SELECT * FROM t2 WHERE `最早打卡` >'08:30:00' AND `最晚打卡` <'17:30:00' AND f4 !='星期日' AND f4 !='星期六' GROUP BY `姓名`,`日期时间`


-- 
-- SELECT k.*,'全天缺勤' from d INNER join u on 1=1 LEFT join k on u.`姓名`= k.`姓名` order by d.`日期时间`,k.`姓名`;
-- 

select b.*,'全天缺勤' from k a
right join (select * from u b,(select distinct `日期时间` from d) c) b on a.`姓名`=b.`姓名` and a.`日期时间`=b.`日期时间`
WHERE a.`姓名` IS NULL
--

　　

结果如下

好了，可以给小姐姐了。