急，高分求答！写一段mysql脚本，查询某个部门所有员工的考勤明细和考勤状态，要完整可用的sql脚本
有四张表，分别是部门，员工，签到，时间（是否工作日），表结构如下:
部门department：
员工employee:
签到sign_record:
时间deal_calendar:
表结构和测试数据sql脚本：
/*
Navicat MySQL Data Transfer
Source Server : guangda
Source Server Version : 80013
Source Host : 127.0.0.1:3306
Source Database : guangda
Target Server Type : MYSQL
Target Server Version : 80013
File Encoding : 65001
Date: 2019-01-25 09:08:09
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for deal_calendar
-- ----------------------------
DROP TABLE IF EXISTS `deal_calendar`;
CREATE TABLE `deal_calendar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL COMMENT '交易日历表',
`isDealDay` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'N' COMMENT '是否交易日，Y是，N不是',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of deal_calendar
-- ----------------------------
INSERT INTO `deal_calendar` VALUES ('1', '2018-09-01', 'N');
INSERT INTO `deal_calendar` VALUES ('2', '2018-09-02', 'N');
INSERT INTO `deal_calendar` VALUES ('3', '2018-09-03', 'Y');
INSERT INTO `deal_calendar` VALUES ('4', '2018-09-04', 'Y');
INSERT INTO `deal_calendar` VALUES ('5', '2018-09-05', 'Y');
INSERT INTO `deal_calendar` VALUES ('6', '2018-09-06', 'Y');
INSERT INTO `deal_calendar` VALUES ('7', '2018-09-07', 'Y');
INSERT INTO `deal_calendar` VALUES ('8', '2018-09-08', 'N');
INSERT INTO `deal_calendar` VALUES ('9', '2018-09-09', 'N');
INSERT INTO `deal_calendar` VALUES ('10', '2018-09-10', 'Y');
INSERT INTO `deal_calendar` VALUES ('11', '2018-09-11', 'Y');
INSERT INTO `deal_calendar` VALUES ('12', '2018-09-12', 'Y');
INSERT INTO `deal_calendar` VALUES ('13', '2018-09-13', 'Y');
INSERT INTO `deal_calendar` VALUES ('14', '2018-09-14', 'Y');
INSERT INTO `deal_calendar` VALUES ('15', '2018-09-15', 'N');
INSERT INTO `deal_calendar` VALUES ('16', '2018-09-16', 'N');
INSERT INTO `deal_calendar` VALUES ('17', '2018-09-17', 'Y');
INSERT INTO `deal_calendar` VALUES ('18', '2018-09-18', 'Y');
INSERT INTO `deal_calendar` VALUES ('19', '2018-09-19', 'Y');
INSERT INTO `deal_calendar` VALUES ('20', '2018-09-20', 'Y');
INSERT INTO `deal_calendar` VALUES ('21', '2018-09-21', 'Y');
INSERT INTO `deal_calendar` VALUES ('22', '2018-09-22', 'N');
INSERT INTO `deal_calendar` VALUES ('23', '2018-09-23', 'N');
INSERT INTO `deal_calendar` VALUES ('24', '2018-09-24', 'Y');
INSERT INTO `deal_calendar` VALUES ('25', '2018-09-25', 'Y');
INSERT INTO `deal_calendar` VALUES ('26', '2018-09-26', 'Y');
INSERT INTO `deal_calendar` VALUES ('27', '2018-09-27', 'Y');
INSERT INTO `deal_calendar` VALUES ('28', '2018-09-28', 'Y');
INSERT INTO `deal_calendar` VALUES ('29', '2018-09-29', 'N');
INSERT INTO `deal_calendar` VALUES ('30', '2018-09-30', 'N');
-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名字',
`status` int(11) DEFAULT NULL COMMENT '部门状态 0不可用,1可用',
`no_permission_floors` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '无权限进入的门',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO `department` VALUES ('1', '固定收益部', '1', null);
INSERT INTO `department` VALUES ('2', '资本市场部', '1', null);
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`faceId` varchar(255) DEFAULT NULL COMMENT '注册人脸库人脸id',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工姓名',
`empNO` varchar(64) NOT NULL COMMENT '员工编号',
`cardNO` int(32) DEFAULT NULL,
`postId` int(11) DEFAULT NULL COMMENT '关联岗位表id',
`dept` int(11) DEFAULT NULL COMMENT '关联部门表id',
`password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工密码',
`entryTime` date DEFAULT NULL COMMENT '入职时间',
`birthday` date DEFAULT NULL COMMENT '员工生日',
`sex` char(1) DEFAULT NULL COMMENT '性别，M男，F女',
`isblacklist` int(2) DEFAULT '0' COMMENT '是否黑名单 0不是，1是',
`vip` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'N' COMMENT '是否vip，N不是，Y是',
`tel` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '电话号码',
`img` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '头像地址',
`status` int(2) DEFAULT '1' COMMENT '0无效，1有效',
`faceToken` varchar(255) DEFAULT NULL COMMENT '人脸token',
`updateTime` datetime DEFAULT NULL COMMENT '更新时间',
`remark` varchar(255) DEFAULT NULL COMMENT '备注，VIP客户企业',
UNIQUE KEY `主索引` (`id`),
UNIQUE KEY `uk_empNo` (`empNO`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', null, '张三', 'zhangsan', '1', null, '1', null, null, '1990-10-12', 'F', '0', 'N', null, null, '1', 'zhangsan', null, null);
INSERT INTO `employee` VALUES ('2', null, '李四', 'lisi', '3', null, '1', null, null, '1982-12-16', 'M', '0', 'N', null, null, '1', 'lisi', null, null);
INSERT INTO `employee` VALUES ('3', null, '王五', 'wangwu', '2', null, '2', null, null, '1990-02-01', 'M', '0', 'N', null, null, '1', 'wangwu', null, null);
-- ----------------------------
-- Table structure for sign_record
-- ----------------------------
DROP TABLE IF EXISTS `sign_record`;
CREATE TABLE `sign_record` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序列号',
`empNO` varchar(32) DEFAULT NULL COMMENT '员工号',
`confidence` float DEFAULT NULL COMMENT '比对相似度',
`cardNo` varchar(32) DEFAULT NULL COMMENT '门禁卡号',
`signTime` datetime DEFAULT NULL COMMENT '签到时间',
`deviceNo` int(11) DEFAULT NULL COMMENT '设备号',
`imagePath` varchar(255) DEFAULT NULL COMMENT '头像路径',
`type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '类型，0普通，1生日，2入职100天，3入职1000天，4，最早到，5本月全勤,6陌生人，7黑名单，8领导层',
`IO` char(1) DEFAULT NULL COMMENT '进出标识',
`source` int(1) DEFAULT NULL COMMENT '打卡数据来源，0人',
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
UNIQUE KEY `主索引` (`id`),
KEY `index_sign_time` (`signTime`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sign_record
-- ----------------------------
INSERT INTO `sign_record` VALUES ('8', 'zhangsan', null, '1', '2018-09-24 19:59:33', '512', null, '4', null, '0', '');
INSERT INTO `sign_record` VALUES ('9', 'wangwu', null, '2', '2018-09-24 20:00:26', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('24', 'lisi', null, '3', '2018-09-24 07:32:53', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('26', 'lisi', null, '3', '2018-09-24 18:53:42', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('59', 'lisi', null, '3', '2018-09-30 09:08:37', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('60', 'lisi', null, '3', '2018-09-30 18:09:16', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('61', 'kesc', null, '2', '2018-09-29 07:20:58', '512', null, '0', null, '0', null);
INSERT INTO `sign_record` VALUES ('62', 'zhangsan', null, '1', '2018-09-26 12:22:01', '512', null, '0', null, '0', null);
完整需求：
1.员工号，姓名，月份，工作日天数，是否全勤，正常天数，迟到天数，早退天数，迟到加早退天数，全天缺席天数
2.员工号，姓名，日期，考勤类别(正常，迟到，早退，迟到加早退，全天缺席)
补充：早上8点后迟到，下午5点半前早退，非工作日加班不用计算迟到或早退
你这个如果忘记打卡是缺勤还是算啥
在你的数据基础上写的。
先写第二个，第一个在第二个基础上统计下就行了。
-- 员工号，姓名，日期，考勤类别(正常，迟到，早退，迟到加早退，全天缺席)
-- 补充：早上8点后迟到，下午5点半前早退，非工作日加班不用计算迟到或早退
SELECT
deal.date,
employee.name,
tab.empNO,
tab.cqType
FROM
deal_calendar AS deal
INNER JOIN (
SELECT
CASE
WHEN
MIN( signTime ) <= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) >= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'正常'
WHEN COUNT( empNO ) = 1
THEN
'打卡一次，算迟到还是算早退呢'
WHEN MIN( signTime ) > CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) > CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'迟到'
WHEN MIN( signTime ) <= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) < CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'早退' ELSE '迟到加早退'
END AS 'cqType',
empNO,
DATE_FORMAT( signTime, '%Y-%m-%d' ) AS date
FROM
sign_record
GROUP BY
DATE_FORMAT( signTime, '%Y-%m-%d' ),
empNO
) AS tab ON tab.date = deal.date
LEFT JOIN employee AS employee ON employee.empNO = tab.empNO
WHERE
deal.isDealDay = 'Y'
-- 筛选指定月份
-- AND DATE_FORMAT(deal.date,'%Y-%m') ='2018-09'
UNION ALL
SELECT
deal.date,
employee.name,
employee.empNO,
'全天缺勤'
FROM
deal_calendar AS deal
LEFT JOIN employee AS employee ON deal.date = deal.date
LEFT JOIN sign_record AS sign ON sign.empNO = employee.empNO
AND deal.date = DATE_FORMAT( sign.signTime, '%Y-%m-%d' )
WHERE
deal.isDealDay = 'Y'
AND sign.id IS NULL
-- 筛选指定月份
-- AND DATE_FORMAT(deal.date,'%Y-%m') ='2018-09'
ORDER BY
date ASC
测试结果
第一个在第二个sql上统计下
-- 员工号，姓名，月份，工作日天数，是否全勤，正常天数，迟到天数，早退天数，迟到加早退天数，全天缺席天数
SELECT
tab.`name`,
tab.empNO,
DATE_FORMAT(tab.date,'%c') AS month,
COUNT(tab.cqType) AS workCount,
COUNT(tab.cqType ='正常' or null) AS normalCount,
COUNT(tab.cqType ='迟到' or null) AS lateCount,
COUNT(tab.cqType ='早退' or null) AS earlyCount,
COUNT(tab.cqType ='迟到加早退' or null) AS lateAndEarlyCount,
COUNT(tab.cqType ='全天缺勤' or null) AS absenteeismCount,
COUNT(tab.cqType ='打卡一次，算迟到还是算早退呢' or null) AS absenteeismCount
FROM (
SELECT
deal.date,
employee.name,
tab.empNO,
tab.cqType
FROM
deal_calendar AS deal
INNER JOIN (
SELECT
CASE
WHEN
MIN( signTime ) <= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) >= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'正常'
WHEN COUNT( empNO ) = 1
THEN
'打卡一次，算迟到还是算早退呢'
WHEN MIN( signTime ) > CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) > CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'迟到'
WHEN MIN( signTime ) <= CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 08:00:00' )
AND MAX( signTime ) < CONCAT( DATE_FORMAT( signTime, '%Y-%m-%d' ), ' 17:30:00' )
THEN
'早退' ELSE '迟到加早退'
END AS 'cqType',
empNO,
DATE_FORMAT( signTime, '%Y-%m-%d' ) AS date
FROM
sign_record
GROUP BY
DATE_FORMAT( signTime, '%Y-%m-%d' ),
empNO
) AS tab ON tab.date = deal.date
LEFT JOIN employee AS employee ON employee.empNO = tab.empNO
WHERE
deal.isDealDay = 'Y'
UNION ALL
SELECT
deal.date,
employee.name,
employee.empNO,
'全天缺勤'
FROM
deal_calendar AS deal
LEFT JOIN employee AS employee ON deal.date = deal.date
LEFT JOIN sign_record AS sign ON sign.empNO = employee.empNO
AND deal.date = DATE_FORMAT( sign.signTime, '%Y-%m-%d' )
WHERE
deal.isDealDay = 'Y'
AND sign.id IS NULL
) AS tab
-- 年份筛选
-- WHERE DATE_FORMAT(tab.date,'%Y') = '2018'
GROUP BY DATE_FORMAT(tab.date,'%m'),tab.empNO
测试结果
你这个筛选条件应该是要添加的吧，感觉这样的表设计，sql报表好难啊。
有什么问题在沟通！
希望采纳，谢谢
SELECT 员工号,卡号,姓名,日期,
'打卡记录' AS 考勤类型,
Substring(日期, 1, 4) AS 年份,
Substring(日期, 6, 2) AS 月份,
Datename(weekday, 日期) AS 工作日类型,
Min(打卡时间) AS 上班打卡时间,
Max(打卡时间) AS 下班打卡时间,
CASE
WHEN Datename(weekday, 日期) <> '星期六'
AND Datename(weekday, 日期) <> '星期日' THEN( CASE
WHEN Min(打卡时间) = Max(打卡时间) THEN'上班或下班忘打卡'
WHEN Min(打卡时间) > '08:00:00' THEN '迟到'
WHEN Max(打卡时间) < '17:00:00' THEN '早退'
ELSE '正常'
END )
ELSE '非工作日打卡'
END AS 状态
FROM (SELECT a.[emp_id] 员工号,
a.[card_id] 卡号,
b.[emp_fname] 姓名,
CONVERT(CHAR(10), sign_time, 120) 日期,
CONVERT(VARCHAR, sign_time, 108) 打卡时间,
[sign_time]
FROM [dbo].[TimeRecords] a
LEFT JOIN [dbo].[Employee] b
ON a.emp_id = b.emp_id
AND a.[card_id] = b.[card_id]
WHERE a.emp_id IS NOT NULL
AND a.emp_id <> ''
AND CONVERT(CHAR(10), sign_time, 120) BETWEEN '2018-09-01' AND '2018-10-30'
AND b.[emp_fname] = '姓名') AS mm
GROUP BY mm.员工号,
卡号,
姓名,
mm.日期
ORDER BY 员工号,
日期 ASC
第一个需求：
存储过程创建：
CREATE PROCEDURE check_work_proc(in deptid INT)
BEGIN
SELECT t1.empNO as ep,t1.name,CONCAT(YEAR(signTime),'-',MONTH(signTime)) AS months,
(SELECT COUNT(*) FROM deal_calendar WHERE CONCAT(YEAR(date),'-',MONTH(date))=months AND isDealDay='y') AS 工作日天数,
CASE WHEN (SELECT COUNT(DISTINCT CONCAT(YEAR(signTime),'-',MONTH(signTime),'-',DAY(signTime))) FROM sign_record WHERE CONCAT(YEAR(signTime),'-',MONTH(signTime))=months AND empNO=ep)=(SELECT COUNT(*) FROM deal_calendar WHERE CONCAT(YEAR(date),'-',MONTH(date))=months) then 'y' ELSE 'n' END AS 是否全勤,
(SELECT COUNT(*) FROM (SELECT DATE(signTime) AS dt,empNO FROM sign_record WHERE signTime in(SELECT min(signTime) FROM sign_record GROUP BY date(signTime)) AND DATE_FORMAT(signTime,"%H:%i:%s")<='08:00:00') t3
INNER JOIN
(SELECT DATE(signTime) AS dt,empNO FROM sign_record WHERE signTime in(SELECT max(signTime) FROM sign_record GROUP BY date(signTime)) AND DATE_FORMAT(signTime,"%H:%i:%s")>='17:30:00') t4
ON
t3.dt=t4.dt AND t3.empNO=t4.empNO
WHERE t3.empNO=ep AND CONCAT(YEAR(t3.dt),'-',MONTH(t3.dt))=months)
AS 正常天数,
(SELECT COUNT(*) FROM sign_record WHERE signTime in(SELECT min(signTime) FROM sign_record WHERE empNO=ep AND CONCAT(YEAR(signTime),'-',MONTH(signTime))=months GROUP BY date(signTime)) AND DATE_FORMAT(signTime,"%H:%i:%s")>'08:00:00' AND DATE(signTime) NOT in(SELECT date FROM deal_calendar WHERE isDealDay='N')) AS 迟到,
(SELECT COUNT(*) FROM sign_record WHERE signTime in(SELECT max(signTime) FROM sign_record WHERE empNO=ep AND CONCAT(YEAR(signTime),'-',MONTH(signTime))=months GROUP BY date(signTime)) AND DATE_FORMAT(signTime,"%H:%i:%s")<'17:30:00' AND DATE(signTime) NOT in(SELECT date FROM deal_calendar WHERE isDealDay='N')) AS 早退,
(SELECT COUNT(*) FROM (SELECT DATE(signTime) AS dt,empNO FROM sign_record WHERE signTime in(SELECT min(signTime) FROM sign_record GROUP BY date(signTime),empNO) AND DATE_FORMAT(signTime,"%H:%i:%s")>'08:00:00') t3
INNER JOIN
(SELECT DATE(signTime) AS dt,empNO FROM sign_record WHERE signTime in(SELECT max(signTime) FROM sign_record GROUP BY date(signTime),empNO) AND DATE_FORMAT(signTime,"%H:%i:%s")<'17:30:00') t4
ON
t3.dt=t4.dt AND t3.empNO=t4.empNO
WHERE t3.empNO=ep AND CONCAT(YEAR(t3.dt),'-',MONTH(t3.dt))=months AND t3.dt NOT in(SELECT date FROM deal_calendar WHERE isDealDay='N')) AS 迟到加早退,
(SELECT COUNT(*) FROM deal_calendar WHERE date NOT in(SELECT DISTINCT CONCAT(YEAR(signTime),'-',MONTH(signTime),'-',DAY(signTime)) FROM sign_record WHERE CONCAT(YEAR(signTime),'-',MONTH(signTime))=months AND empNO=ep ) AND CONCAT(YEAR(date),'-',MONTH(date))=months AND isDealDay='y') AS 全天缺席
FROM employee t1
LEFT JOIN
sign_record t2
ON
t1.empNO=t2.empNO
WHERE dept=1
GROUP BY 1,3;
END
存储过程调用：
CALL check_work_proc(1);
测试结果：