本文共 5421 字，大约阅读时间需要 18 分钟。

查询人员考勤记录

人员计划表

打卡时间表

需求:写一个查询语句，输入参数：日期（date），输出表格如下：

代码部分

#创建数据库CREATE DATABASE IF NOT EXISTS 0613_practice;#创建人员表CREATE TABLE IF NOT EXISTS workPlan(	workerName VARCHAR(10) COMMENT '人员',	startDate DATE COMMENT '派工起始日',	endDate DATE COMMENT '派工截止日',	signTime VARCHAR(10) COMMENT '派工期间的需要打卡时间');#人员表插入数据INSERT INTO workPlan VALUES('张三','2020-01-01',NULL,'06:30'),('李四','2020-02-01','2020-02-15','07:00'),('王五','2019-12-29','2020-03-30','06:00'),('赵六','2019-12-29','2020-03-30','06:00');/*1、表示某人从某日开始到某日结束，按要求工作，派工期间每日打卡时间必须在“要求到岗时间”前（含要求时间，精确到分钟），否则迟到。例如：要求7：00，则6:59或者7:00:59 都不算迟到；7:01则视为迟到1分钟2、行1中“派工结束日期”为null，表示此人的工作结束时间尚未确定，还在搬砖中；行2中派工结束日期为2020-02-15，表示派工于02-15日结束。3、假设员工名字不重复，每人只有一条派工信息*/#创建考勤记录表CREATE TABLE IF NOT EXISTS signLog(	workerName VARCHAR(10),	signTime DATETIME);#考勤记录表插入数据INSERT INTO signLog VALUES  ('张三','2020-02-16 04:01'),('张三','2020-02-16 05:02'),('张三','2020-02-16 06:03'),('王五','2020-02-16 07:03'),('王五','2020-02-16 08:03'),('王五','2020-02-16 09:03');#打卡记录表在员工每次按指纹考勤时都会生成一条记录#思路分析/*先按照日期查找该结束日期在参数日期之后的人或者结束日期为空再查找当天考勤记录表(按人名分组找出最早时间的人)与过滤后的人员表对应若该表记录少了某人即当日没有打卡按迟到1440分算再来看最早打卡时间与人员表的需要时间进行运算判断是否迟到若迟到 计算时间 状态为是若没迟到 迟到时间为0 状态为否*/#测试时间转整形并运算SELECT (CONVERT(DATE_FORMAT(CONVERT('06:30',TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT('06:30',TIME),"%i"),UNSIGNED INTEGER))小时转分钟;SELECT(CONVERT(DATE_FORMAT('2020-02-16 04:01',"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT('2020-02-16 04:01',"%i"),UNSIGNED INTEGER));SELECT DATE_FORMAT('2020-02-16 04:01',"%H");SELECT DATE_FORMAT('2020-02-16 04:01',"%i");#1)结束日期在参数日期之后的人或者结束日期为空SELECT w.workerName,w.signTimeFROM workPlan wWHERE UNIX_TIMESTAMP(w.endDate) >=UNIX_TIMESTAMP('2020-02-16') OR w.endDate IS NULL;#2)按名字分组最早打卡SELECT s.workerName,s.signTimeFROM signLog sWHERE CONVERT(signTime,DATE)='2020-02-16'GROUP BY s.workerNameHAVING MIN(signTime);#3)未打卡即矿工的人SELECT a.workerNameFROM(SELECT workerNameFROM workPlan wWHERE UNIX_TIMESTAMP(w.endDate) >=UNIX_TIMESTAMP('2020-02-16') OR w.endDate IS NULL) aWHERE a.workerName  NOT IN (SELECT s.workerName FROM signLog s WHERE CONVERT(signTime,DATE)='2020-02-16'GROUP BY s.workerName HAVING MIN(signTime));#4)正常打卡需要计算的人#5)合并全部表SELECT s.workerName 姓名,CONVERT(s.signTime,DATE) 日期,(CASE 					WHEN (CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))<= 					     (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))						THEN '是'					ELSE '否'				END) 是否迟到				,(CASE 					WHEN (CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))<= 					     (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))						THEN (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))-(CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))					ELSE 0				END) 迟到时间FROM signLog s JOIN workPlan w ON w.workerName= s.workerNameWHERE CONVERT(s.signTime,DATE)='2020-02-16'GROUP BY s.workerNameHAVING MIN(s.signTime) UNIONSELECT a.workerName,CONVERT('2020-02-16',DATE) 日期,(CASE							WHEN TRUE THEN '是'						   END)是否迟到,						   (CASE							WHEN TRUE THEN '1440'						   END )迟到时间FROM	(SELECT workerName	FROM workPlan w	WHERE UNIX_TIMESTAMP(w.endDate) >=UNIX_TIMESTAMP('2020-02-16') OR w.endDate IS NULL) aWHERE a.workerName  NOT IN (SELECT s.workerName FROM signLog s WHERE CONVERT(signTime,DATE)='2020-02-16'GROUP BY s.workerName HAVING MIN(signTime));#创建存储过程参数为时间d-->DELIMITER $$CREATE PROCEDURE getDateSignLog(IN d DATE)BEGIN	SELECT s.workerName 姓名,CONVERT(s.signTime,DATE) 日期,(CASE 					WHEN (CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))<= 					     (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))						THEN '是'					ELSE '否'				END) 是否迟到				,(CASE 					WHEN (CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))<= 					     (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))						THEN (CONVERT(DATE_FORMAT(s.`signTime`,"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(s.`signTime`,"%i"),UNSIGNED INTEGER))-(CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%H"),UNSIGNED INTEGER)*60+CONVERT(DATE_FORMAT(CONVERT(w.signTime,TIME),"%i"),UNSIGNED INTEGER))					ELSE 0				END) 迟到时间FROM signLog s JOIN workPlan w ON w.workerName= s.workerNameWHERE CONVERT(s.signTime,DATE)=dGROUP BY s.workerNameHAVING MIN(s.signTime) UNIONSELECT a.workerName,CONVERT(d,DATE) 日期,(CASE							WHEN TRUE THEN '是'						   END)是否迟到,						   (CASE							WHEN TRUE THEN '1440'						   END )迟到时间FROM	(SELECT workerName	FROM workPlan w	WHERE UNIX_TIMESTAMP(w.endDate) >=UNIX_TIMESTAMP(d) OR w.endDate IS NULL) aWHERE a.workerName  NOT IN (SELECT s.workerName FROM signLog s WHERE CONVERT(signTime,DATE)=d GROUP BY s.workerName HAVING MIN(signTime));END $$DELIMITER ; CALL getDateSignLog('2020-02-14');

转载地址：http://zdjxi.baihongyu.com/