September_Lhi 2019-01-25 11:09 采纳率: 50%
浏览 2310
已采纳

急，高分求答！写一段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点半前早退，非工作日加班不用计算迟到或早退

  • 写回答

3条回答 默认 最新

  • 路漫漫兮其修远兮 2019-01-25 15:29
    关注

    你这个如果忘记打卡是缺勤还是算啥

    在你的数据基础上写的。
    先写第二个，第一个在第二个基础上统计下就行了。

    -- 员工号，姓名，日期，考勤类别(正常，迟到，早退，迟到加早退，全天缺席)
-- 补充：早上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报表好难啊。
    有什么问题在沟通！
    希望采纳，谢谢

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题？

悬赏问题

  • ¥30 这是哪个作者做的宝宝起名网站
  • ¥60 版本过低apk如何修改可以兼容新的安卓系统
  • ¥25 由IPR导致的DRIVER_POWER_STATE_FAILURE蓝屏
  • ¥50 有数据，怎么建立模型求影响全要素生产率的因素
  • ¥50 有数据，怎么用matlab求全要素生产率
  • ¥15 TI的insta-spin例程
  • ¥15 完成下列问题完成下列问题
  • ¥15 C#算法问题, 不知道怎么处理这个数据的转换
  • ¥15 YoloV5 第三方库的版本对照问题
  • ¥15 请完成下列相关问题！