September_Lhi
September_Lhi
采纳率50%
2019-01-25 11:09 阅读 2.0k

急,高分求答!写一段mysql脚本,查询某个部门所有员工的考勤明细和考勤状态,要完整可用的sql脚本

500

有四张表,分别是部门,员工,签到,时间(是否工作日),表结构如下:

部门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条回答 默认 最新

  • 已采纳
    it_zhangwei 路漫漫兮其修远兮 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报表好难啊。
    有什么问题在沟通!
    希望采纳,谢谢

    点赞 评论 复制链接分享
  • devmiao devmiao 2019-01-25 11:46
    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 
    
    
    点赞 评论 复制链接分享
  • qq_36720114 德玛洗牙 2019-01-25 16:02

    第一个需求:
    存储过程创建:

    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);
    

    测试结果:
    图片说明

    点赞 评论 复制链接分享

相关推荐