September_Lhi
September_Lhi
2019-01-25 11:09

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

500
  • mysql

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

部门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条回答