SQL问题：想要请教大家对于员工考勤打卡数据的处理_course

SQL问题：想要请教大家对于员工考勤打卡数据的处理 如果一个员工ID对应一天内多条打卡数据，数据格式是‘年月日 小时分秒’，如何把这一列值从varchar变成时间格式的days，并且计算同一个员工ID

sql查询，将同一用户多条打卡记录合并成一条输出，哪位大佬指教一下，非常感谢！_course

 如果上班或者下班没有打卡，则对应的时间位置备注都为空

SQL 数据库考勤为打卡记录查询_course

现在有一张表A，存放员工的打开记录，一张表B，记录每个月除了双休日的具体日期，现在想用两个表对比，查询出未打卡的员工名单，请大神帮忙解决下！ 表A 1001 素娟 人事部 2014/10/9 08:43 18:16 1001 素娟 人事部 2014/10/10 08:56 18:16 1001 素娟 人事部 2014/10/14 08:55 18:18 1001 素娟 人事部 2014/10/15 08:50 18:16 1001 素娟 人事部 2014/10/16 08:49 18:16 1002 娟 人事部 2014/10/9 08:43 18:16 1002 娟 人事部 2014/10/10 08:56 18:16 1002 娟 人事部 2014/10/15 08:50 18:16 1002 娟 人事部 2014/10/16 08:49 18:16 ... 表B 2014-10-06 16:37:54.007 2014-10-07 16:37:54.007 2014-10-08 16:37:54.007 2014-10-09 16:37:54.007

求一考勤统计实现SQL代码_course

目前有两张表如下 ``` --用户当日班次表 IF OBJECT_ID('[userKQ]') IS NOT NULL DROP TABLE userKQ CREATE TABLE userKQ ( dateKQ VARCHAR (10), --日期 userid VARCHAR(20), --员工ID banci VARCHAR(20), --班次名称 StartTime DATETIME, --上班时间 EndTime DATETIME, --下班时间 StartTime1 DATETIME, --上班有效打卡时间开始 StartTime2 DATETIME, --上班有效打卡时间结束 EndTime1 DATETIME, --下班有效打卡时间开始 EndTime2 DATETIME --下班有效打卡时间结束 ) INSERT INTO userKQ SELECT '2017-03-02','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL SELECT '2017-03-02','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL SELECT '2017-03-02','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL SELECT '2017-03-02','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL SELECT '2017-03-02','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000' UNION ALL SELECT '2017-03-03','000015','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL SELECT '2017-03-03','000015','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL SELECT '2017-03-03','000002','上午','1900-01-01 08:00:00.000','1900-01-01 12:00:00.000','1900-01-01 07:30:00.000','1900-01-01 08:30:00.000','1900-01-01 11:30:00.000','1900-01-01 12:30:00.000' UNION ALL SELECT '2017-03-03','000002','下午','1900-01-01 14:00:00.000','1900-01-01 18:00:00.000','1900-01-01 13:30:00.000','1900-01-01 14:30:00.000','1900-01-01 17:30:00.000','1900-01-01 18:30:00.000' UNION ALL SELECT '2017-03-03','000003','上下午','1900-01-01 09:00:00.000','1900-01-01 17:00:00.000','1900-01-01 08:30:00.000','1900-01-01 09:30:00.000','1900-01-01 16:30:00.000','1900-01-01 17:30:00.000' --考勤原始记录表 IF OBJECT_ID('[checkinout]') IS NOT NULL DROP TABLE checkinout CREATE TABLE checkinout ( userid VARCHAR(20), --员工ID checktime DATETIME --打卡时间 ) INSERT INTO checkinout -- SELECT '000014','2017-03-02 07:51:00.000' UNION ALL SELECT '000015','2017-03-02 07:12:00.000' UNION ALL SELECT '000015','2017-03-02 07:33:00.000' UNION ALL SELECT '000015','2017-03-02 07:54:00.000' UNION ALL SELECT '000015','2017-03-02 12:14:00.000' UNION ALL SELECT '000015','2017-03-02 12:34:00.000' UNION ALL SELECT '000015','2017-03-02 13:50:00.000' UNION ALL --SELECT '000015','2017-03-02 18:01:00.000' UNION ALL SELECT '000002','2017-03-02 07:54:00.000' UNION ALL SELECT '000002','2017-03-02 12:14:00.000' UNION ALL --SELECT '000002','2017-03-02 13:50:00.000' UNION ALL SELECT '000002','2017-03-02 18:01:00.000' UNION ALL SELECT '000003','2017-03-02 08:30:00.000' UNION ALL SELECT '000003','2017-03-02 16:59:00.000' UNION ALL SELECT '000003','2017-03-02 17:15:00.000' UNION ALL --SELECT '000015','2017-03-02 07:34:00.000' UNION ALL SELECT '000015','2017-03-03 12:01:00.000' UNION ALL SELECT '000015','2017-03-03 13:34:00.000' UNION ALL SELECT '000015','2017-03-03 18:01:00.000' UNION ALL SELECT '000002','2017-03-03 07:54:00.000' UNION ALL SELECT '000002','2017-03-03 12:14:00.000' UNION ALL SELECT '000002','2017-03-03 13:50:00.000' UNION ALL --SELECT '000002','2017-03-03 18:01:00.000' UNION ALL SELECT '000003','2017-03-03 17:15:00.000' --在有效打卡时间段内有多次打卡记录的,取第一次打卡记录,没有打卡记录或不在有效打卡记录时间段的不记考勤,标记NULL --求实现如下效果,CHECKTIME1和CHECKTIME2分别是有效打卡时间,双手奉上仅有的10分,求高手帮忙. /* dateKQ USERID BANCI StartTime EndTime CHECKTIME1 CHECKTIME2 2017-03-02 000015 上午 1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-02 07:33:00.000 2017-03-02 12:14:00.000 2017-03-02 000015 下午 1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-02 13:50:00.000 null 2017-03-02 000002 上午 1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-02 07:54:00.000 2017-03-02 12:14:00.000 2017-03-02 000002 下午 1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 null 2017-03-02 18:01:00.000 2017-03-02 000003 上下午 1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 2017-03-02 08:30:00.000 2017-03-02 16:59:00.000 2017-03-03 000015 上午 1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-02 07:33:00.000 2017-03-02 12:14:00.000 2017-03-03 000015 下午 1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-02 13:50:00.000 null 2017-03-03 000002 上午 1900-01-01 08:00:00.000 1900-01-01 12:00:00.000 2017-03-03 07:54:00.000 2017-03-02 12:14:00.000 2017-03-03 000002 下午 1900-01-01 14:00:00.000 1900-01-01 18:00:00.000 2017-03-03 13:50:00.000 null 2017-03-03 000003 上下午 1900-01-01 09:00:00.000 1900-01-01 17:00:00.000 null 2017-03-03 17:15:00.000 */ ```

删除考勤重复刷卡记录？_course

1. ```FOR DELETE,Update AS 1. delete from onoffdutydata where onoffdutyid in ( select a.onoffdutyid From onoffdutd in ( select a.onoffdutyid From onoffdutydata a inner join employeemsg b on a.employeeid=b.employeeid and a.checkdate<b.ondutytime) 1. delete from employeesalary where salaryid in ( select a.salaryid From employeesalary a inner join employeemsg b on a.employeeid=b.employeeid and a.Yearmonth>convert(nvarchar(7),outdutytime,21)) 1. delete from employeesalary where salaryid in ( select a.salaryid From employeesalary a inner join employeemsg b on a.employeeid=b.employeeid and a.Yearmonth<convert(nvarchar(7),ondutytime,21)) 1. delete from originalcheckdata where originaldataid in (select a.originaldataid from originalcheckdata a inner join originalcheckdata b on a.employeeid=b.employeeid and a.occurtime=b.occurtime and a.originaldataid>b.originaldataid) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ``` 

数据库写需求分析的数据流图——员工考勤系统_course

分层画数据流图，类似这样的：l 最好分三层 实现的功能是员工打卡，经理查看、修改员工信息；查看考勤统计和修改考勤统计  第一次写这个不知道怎么下手

sql 组语句 关于考勤的计算_course

1.需求是想计算如下表数据，计算出每个人的有效工时，用到的字段列为： 姓名（name）、 打卡日期（attendancedate）、 打卡时间（attendancetime）、 打卡位置(attendancearea)  2.关键点在于例如有中间出去吃饭的张三，他一天打了四次卡，06时、12时 、13时、17时，怎样做到在数据库计算中，得出有效出工时间，默认上班是没有规定时间的，只是中间出去可能吃午饭会再次刷一次出门卡和一次进门卡，怎么扣除出去的时间，计算得出有效的在公司内的有效工时，例如张三出去吃饭一小时，那这一天的有效工时是不会包含中午吃饭的一小时的，还有一种可能就是中间多刷了一次下班卡，或者多刷了一次上班卡，或者N次，但是没有一个进一个出对应起来的都算异常，怎样将这个标记为异常不计算标记查询出来的数据为 异常数据。请高手指教一下。感激不尽 3.图中例子只是参考，会有很多人很多打卡数据的，也会有很多天的，希望可以支持区间多日查询有total有效工时的计算，请给出sql语句，拼出显示数据的t-sql，可以执行并看见效果。可以用临时表计算存储过程，种类不限，可用就行，急急急 效果如图，拼当日计算语句就行了，报表这个按月列出每日的我自己做 

有个MySQL考勤表，如何统计员工上下班并且带上其他字段？_course

现在有个考勤表为 我要统计出他每天的上下班打卡时间还有打卡时的照片，按每天最大最小的时间排序已经解决了，现在如何带上img_url这个字段呢。 如图显示

求助拼sql语句，考勤类相关_course

1.需求是想计算如下表数据，计算出每个人的有效工时，用到的字段列为： 姓名（name）、 打卡日期（attendancedate）、 打卡时间（attendancetime）、 打卡位置(attendancearea)  2.关键点在于例如有中间出去吃饭的张三，他一天打了四次卡，06时、12时 、13时、17时，怎样做到在数据库计算中，得出有效出工时间，默认上班是没有规定时间的，只是中间出去可能吃午饭会再次刷一次出门卡和一次进门卡，怎么扣除出去的时间，计算得出有效的在公司内的有效工时，例如张三出去吃饭一小时，那这一天的有效工时是不会包含中午吃饭的一小时的，还有一种可能就是中间多刷了一次下班卡，或者多刷了一次上班卡，或者N次，但是没有一个进一个出对应起来的都算异常，怎样将这个标记为异常不计算标记查询出来的数据为 异常数据。请高手指教一下。感激不尽 3.图中例子只是参考，会有很多人很多打卡数据的，也会有很多天的，希望可以支持区间多日查询有total有效工时的计算，请给出sql语句，拼出显示数据的t-sql，可以执行并看见效果。可以用临时表计算存储过程，种类不限，可用就行，急急急 效果如图，拼当日计算语句就行了，报表这个按月列出每日的我自己做 

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

有四张表，分别是部门，员工，签到，时间（是否工作日），表结构如下: 部门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点半前早退，非工作日加班不用计算迟到或早退

如何把同一天的打卡记录 显示在同一行_course

原表： id dksj 1 2016-4-15 08:00:01 2 2016-4-15 11:31:01 3 2016-4-15 13:00:00 4 2016-4-15 17:01:21 5 2016-4-16 08:02:00 6 2016-4-16 11:35:01 希望得到的结果： date daka 2016-4-15 08：00 11：31 13：00 17：01 2016-4-16 08：02 11：35

计算考勤迟到和早退次数_course

有一张表,怎么算11月份这两个人的迟到次数和早退次数，早9晚5，用java或mysql实现 员工id 上班时间 下班时间 是否加班 1是0否 empId starttime endtime isaddwork 1001 2014-11-12 08：50:43 2014-11-12 18：10:43 1 1001 2014-11-13 08：50:43 2014-11-13 17：50:43 0 1002 2014-11-12 09：10:43 2014-11-12 18：04:43 1 1002 2014-11-13 08：50:43 2014-11-13 18：10:43 0 1001 2014-11-14 08：50:43 2014-11-14 18：10:43 1 1001 2014-11-15 08：50:43 2014-11-15 17：50:43 0 1002 2014-11-14 09：10:43 2014-11-14 18：04:43 1 1002 2014-11-15 08：50:43 2014-11-15 18：10:43 0

sql server在同一表中筛选出两次刷卡记录时间大于等于40分钟的员工数据_course

刷卡进出数据存储在同一表中，姓名有重复的，一个人可能刷了2次，要求选出在时间段2014-12-28 11:00：00到2014-12-28 13:00:00内两次刷卡记录时间间隔大于等于40分钟的员工 logtime （时间 ）logcard （卡号 ）logid （） logname（姓名） logbm （部门）

关于考勤数据的疑问！_course

原始数据如图所示，如何取出如下图效果，夜班考勤数据是跨天打卡，请大神指点！

mysql中sql语句修改的触发器如何触发_course

大神们，小弟是第一次接触触发器，用的工具是navicat for mysql 目前的设计草表是这样的： 个人每日考勤打卡记录表格： ``` CREATE TABLE `tb_customer_clock_in_everyday` ( `customer_clock_ in_everyday_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '个人考勤打卡id', `enterprise_id` int(11) NOT NULL, `department_id` int(11) NOT NULL COMMENT '所属部门id', `customer_clock_in_month_id` int(11) NOT NULL COMMENT '个人每月考勤打卡记录id', `clock_ in _morning_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '早打卡时间', `clock_ in_night _time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '晚打卡时间', `attendance_status` int(11) DEFAULT NULL COMMENT '状态(0正常打卡，1迟到，2早退，3请假，4外勤，5缺卡,6旷工，7休息8 补卡)', `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `create_by` int(11) DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `update_by` int(11) DEFAULT NULL, PRIMARY KEY (`customer_clock_ in_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` 个人每月考勤打卡记录表格： ``` CREATE TABLE `tb_customer_clock_in_month` ( `customer_clock_in_month_id` int(11) NOT NULL AUTO_INCREMENT, `enterprise_id` int(11) DEFAULT NULL, `customer_clock_ in_id` int(11) DEFAULT NULL COMMENT '个人考勤打卡id', `punch_in_times_per_month` int(11) DEFAULT NULL COMMENT '每月打卡次数', `average_monthly_ hours` double DEFAULT NULL COMMENT '每月平均工时', `days_of_attendance_per_month` int(11) DEFAULT NULL COMMENT '每月出勤天数', `monthly_shift` int(11) DEFAULT NULL COMMENT '每月出勤班次', `leave_early_every_month` int(11) DEFAULT NULL COMMENT '每月早退次数', `number_of_tardiness_per_month` int(11) DEFAULT NULL COMMENT '每月迟到次数' `number_of_card_shortages_per_month` int(11) DEFAULT NULL COMMENT '每月缺卡次数', `number_of_absenteeism_per_month` int(11) DEFAULT NULL COMMENT '每月旷工次数', `monthly_field_trips` int(11) DEFAULT NULL COMMENT '每月请假次数', `number_of_overtime_per_month` int(11) DEFAULT NULL COMMENT '每月加班时间', `rest_days_per_month` int(11) DEFAULT NULL COMMENT '每月休息次数', `field_days_per_month` int(11) DEFAULT NULL COMMENT '每月外勤天数', `number_of_days_off_per_month` int(11) DEFAULT NULL COMMENT '每月请假天数', `status` int(11) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `create_by` int(11) DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `update_by` int(11) DEFAULT NULL, PRIMARY KEY (`customer_clock_ in_month_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` 我的触发器思路是这样的：当个人每日考勤卡的状态修改为3时，触发器将每月考勤记录表的每月早退次数+1，可是我的sql总是写不对，百度了很多，还是不行，还望各路大神帮帮忙，教我写一个。

如何通过考勤日报表做出月报表_course

CREATE TABLE `punchCard` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) NOT NULL, `userName` varchar(45) DEFAULT NULL, `punchCardDate` date DEFAULT NULL,打卡日期 `startTime` time DEFAULT NULL,上班打卡时间 `endTime` time DEFAULT NULL,下班打卡时间 `state` varchar(45) DEFAULT NULL,打卡状态（0未打卡,1正常,2异常） `comment` varchar(200) DEFAULT NULL（异常描述） 上面是日报表求问如何做月报表 月报表需求是 要求显示当月每个人每天的打卡状态 求解月报表该如何设计

获取CSV文件（考勤信息）内容放入集合，怎么获取集合中的每人每天的最早和最晚时间。求大神解决。_course

> List<XXattendance> dkinfos = new ArrayList<XXattendance>();//保存读取csv得到的数据 Map<String, List<XXattendance>> map = new HashMap<String, List<XXattendance>>();//分组统计的map集合 public void generic(){ String sbip = null; List<XXattendance> mapDkinfos = new ArrayList<XXattendance>();//map集合中每组ip的对应的对象集合 BufferedReader reader = null; //读写csv数据到list(dkinfos)集合中 try { reader = new BufferedReader(new FileReader("C:\\Users\\Lollipop\\Desktop\\考勤信息.CSV"));//换成你的文件名 String line = null; while((line=reader.readLine())!=null){ String data[] = line.split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分 XXattendance dkinfo = new XXattendance(); dkinfo.setName(data[1]); dkinfo.setN_id(data[2]); dkinfo.setDate(data[3]); dkinfos.add(dkinfo); // System.out.println(data[0]+","+data[1]); // System.out.println(dkinfo.getName()+dkinfo.getN_id()+dkinfo.getDate()); } //遍历集合，将数据分组存入map集合 for(XXattendance dkinfo : dkinfos){ sbip = dkinfo.getN_id(); sbip = dkinfo.getDate(); mapDkinfos = map.get(sbip);//获取对应sbip的集合，第一次获取时为空 if(mapDkinfos == null){//这里如果不进行为空判断，会报空指针异常 mapDkinfos = new ArrayList<XXattendance>(); } mapDkinfos.add(dkinfo);//将新添加的对象加入到对应的sbip的对象集合 map.put(sbip, mapDkinfos); //将每次新添加对象后的对象集合存入对应map中 } Iterator<XXattendance> iter = dkinfos.iterator(); //遍历map集合 for (Map.Entry<String, List<XXattendance>> entry : map.entrySet()) { // System.out.println("考勤号： " + entry.getKey() + "打卡次数：" + entry.getValue().size()); } } catch (Exception e) { e.printStackTrace(); } finally{ try { reader.close(); dkinfos.clear(); map.clear(); mapDkinfos.clear(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } SCV数据： "部门","姓名","考勤号码","日期时间","机器号","编号","比对方式","卡号" ,"王","005","2017/5/2 9:06:13","1","","指纹","" ,"王","005","2017/5/2 10:35:04","1","","指纹","" ,"王","005","2017/5/2 19:21:28","1","","指纹","" ,"王","005","2017/5/2 19:22:43","1","","指纹","" ,"王","005","2017/5/3 13:58:49","1","","密码","" ,"王","005","2017/5/3 14:01:46","1","","密码","" ,"王","005","2017/5/3 14:10:11","1","","密码","" ,"王","005","2017/5/3 16:58:06","1","","指纹","" ,"王","005","2017/5/4 8:57:29","1","","指纹","" ,"王","005","2017/5/4 9:53:30","1","","密码","" ,"王","005","2017/5/4 11:03:16","1","","指纹","" ,"王","005","2017/5/4 12:29:13","1","","指纹","" ,"王","005","2017/5/4 13:58:32","1","","指纹","" ,"王","005","2017/5/4 14:24:46","1","","指纹","" ,"王","005","2017/5/4 14:49:02","1","","指纹","" ,"王","005","2017/5/4 16:22:15","1","","指纹","" ,"王","005","2017/5/4 18:10:31","1","","指纹","" ,"王","005","2017/5/5 8:53:24","1","","指纹","" ,"王","005","2017/5/5 9:21:41","1","","指纹","" ,"王","005","2017/5/5 11:30:13","1","","密码","" ,"王","005","2017/5/5 12:59:00","1","","指纹","" ,"王","005","2017/5/5 13:01:05","1","","密码","" ,"王","005","2017/5/5 13:48:33","1","","指纹","" ,"王","005","2017/5/5 15:38:59","1","","指纹","" ,"王","005","2017/5/5 18:03:22","1","","指纹","" ,"王","005","2017/5/8 10:11:59","1","","指纹","" ,"王","005","2017/5/8 10:36:55","1","","指纹","" ,"王","005","2017/5/8 11:34:26","1","","指纹","" ,"王","005","2017/5/8 11:46:22","1","","指纹","" ,"王","005","2017/5/8 13:01:05","1","","指纹","" ,"王","005","2017/5/8 13:42:16","1","","指纹","" ,"王","005","2017/5/8 15:41:08","1","","指纹","" ,"王","005","2017/5/8 15:44:01","1","","指纹","" ,"王","005","2017/5/8 18:06:13","1","","指纹","" ,"王","005","2017/5/8 18:10:54","1","","指纹","" ,"王","005","2017/5/9 9:02:09","1","","指纹","" ,"王","005","2017/5/9 10:45:04","1","","指纹","" ,"王","005","2017/5/9 15:54:49","1","","指纹","" ,"王","005","2017/5/9 16:21:58","1","","指纹","" ,"王","005","2017/5/12 8:43:06","1","","指纹","" ,"王","005","2017/5/15 8:58:31","1","","指纹","" ,"王","005","2017/5/15 9:07:21","1","","指纹","" ,"王","005","2017/5/15 13:47:58","1","","指纹","" ,"李","007","2017/5/3 18:15:51","1","","指纹","" ,"李","007","2017/5/4 8:46:05","1","","指纹","" ,"李","007","2017/5/4 8:59:47","1","","指纹","" ,"李","007","2017/5/4 11:13:44","1","","指纹","" ,"李","007","2017/5/4 12:08:21","1","","指纹","" ,"李","007","2017/5/4 13:27:26","1","","指纹","" ,"李","007","2017/5/4 15:32:24","1","","指纹","" ,"李","007","2017/5/4 16:50:40","1","","指纹","" ,"李","007","2017/5/4 18:10:17","1","","指纹","" ,"李","007","2017/5/5 8:45:16","1","","指纹","" ,"李","007","2017/5/5 8:55:33","1","","指纹","" ,"李","007","2017/5/5 8:56:18","1","","指纹","" ,"李","007","2017/5/5 16:38:41","1","","指纹","" ,"李","007","2017/5/5 18:03:41","1","","指纹","" ,"李","007","2017/5/8 8:49:04","1","","指纹","" ,"李","007","2017/5/8 8:50:14","1","","指纹","" ,"李","007","2017/5/8 10:03:45","1","","指纹","" ,"李","007","2017/5/8 12:18:39","1","","指纹","" ,"李","007","2017/5/8 12:27:55","1","","指纹","" ,"李","007","2017/5/8 14:02:19","1","","指纹","" ,"李","007","2017/5/8 14:33:45","1","","指纹","" ,"李","007","2017/5/8 18:06:19","1","","指纹","" ,"李","007","2017/5/8 18:18:26","1","","指纹","" ,"李","007","2017/5/9 8:48:39","1","","指纹","" ,"李","007","2017/5/9 8:49:53","1","","指纹","" ,"李","007","2017/5/9 13:33:35","1","","指纹","" ,"李","007","2017/5/9 15:00:58","1","","指纹","" ,"李","007","2017/5/9 17:40:10","1","","指纹","" ,"李","007","2017/5/9 18:07:28","1","","指纹","" ,"李","007","2017/5/12 8:55:34","1","","指纹","" ,"李","007","2017/5/12 8:58:09","1","","指纹","" ,"李","007","2017/5/12 11:11:20","1","","指纹","" ,"李","007","2017/5/12 15:09:12","1","","指纹","" ,"李","007","2017/5/12 16:27:09","1","","指纹","" ,"李","007","2017/5/12 17:56:23","1","","指纹","" ,"李","007","2017/5/12 18:10:22","1","","指纹","" ,"李","007","2017/5/15 8:50:59","1","","指纹","" ,"李","007","2017/5/15 9:06:47","1","","指纹","" ,"张","008","2017/5/2 9:13:02","1","","指纹","" ,"张","008","2017/5/2 16:28:27","1","","指纹","" ,"张","008","2017/5/2 17:38:45","1","","指纹","" ,"张","008","2017/5/3 9:04:40","1","","指纹","" ,"张","008","2017/5/3 9:08:44","1","","指纹","" ,"张","008","2017/5/3 13:56:29","1","","指纹","" ,"张","008","2017/5/4 10:40:16","1","","指纹","" ,"张","008","2017/5/4 11:56:22","1","","指纹","" ,"张","008","2017/5/4 13:17:10","1","","指纹","" ,"张","008","2017/5/4 13:34:48","1","","密码","" ,"张","008","2017/5/4 15:34:28","1","","密码","" ,"张","008","2017/5/4 15:49:44","1","","指纹","" ,"张","008","2017/5/5 10:58:58","1","","指纹","" ,"张","008","2017/5/5 12:34:57","1","","密码","" ,"张","008","2017/5/5 16:45:05","1","","指纹","" ,"张","008","2017/5/8 10:17:23","1","","指纹","" ,"张","008","2017/5/8 10:23:07","1","","指纹","" ,"张","008","2017/5/8 12:10:50","1","","指纹","" ,"张","008","2017/5/8 15:10:49","1","","指纹","" ,"张","008","2017/5/9 10:13:12","1","","指纹","" ,"张","008","2017/5/9 11:51:06","1","","指纹","" ,"张","008","2017/5/9 12:19:36","1","","指纹","" ,"张","008","2017/5/9 13:30:50","1","","指纹","" ,"张","008","2017/5/11 10:13:07","1","","指纹","" ,"张","008","2017/5/11 11:26:39","1","","指纹","" ,"张","008","2017/5/11 12:04:37","1","","指纹","" ,"张","008","2017/5/11 13:56:43","1","","指纹","" ,"张","008","2017/5/15 9:19:06","1","","指纹","" ,"张","008","2017/5/15 12:07:44","1","","指纹","" ,"张","008","2017/5/15 13:14:25","1","","密码","" ,"于","009","2017/5/2 9:10:01","1","","指纹","" ,"于","009","2017/5/2 17:58:15","1","","指纹","" ,"于","009","2017/5/3 9:07:14","1","","指纹","" ,"于","009","2017/5/3 18:15:26","1","","指纹","" ,"于","009","2017/5/4 8:58:18","1","","指纹","" ,"于","009","2017/5/4 9:51:00","1","","指纹","" ,"于","009","2017/5/4 13:56:46","1","","指纹","" ,"于","009","2017/5/4 14:47:43","1","","指纹","" ,"于","009","2017/5/4 15:51:32","1","","指纹","" ,"于","009","2017/5/4 16:49:36","1","","指纹","" ,"于","009","2017/5/4 18:10:11","1","","指纹","" ,"于","009","2017/5/5 8:46:30","1","","指纹","" ,"于","009","2017/5/5 8:59:37","1","","指纹","" ,"于","009","2017/5/5 9:25:21","1","","指纹","" ,"于","009","2017/5/5 9:54:00","1","","指纹","" ,"于","009","2017/5/5 10:31:46","1","","指纹","" ,"于","009","2017/5/5 13:38:04","1","","指纹","" ,"于","009","2017/5/5 14:53:26","1","","指纹","" ,"于","009","2017/5/5 16:39:31","1","","指纹","" ,"于","009","2017/5/5 17:49:45","1","","指纹","" ,"于","009","2017/5/5 18:03:37","1","","指纹","" ,"于","009","2017/5/8 8:48:25","1","","指纹","" ,"于","009","2017/5/8 9:48:53","1","","指纹","" ,"于","009","2017/5/8 12:42:48","1","","指纹","" ,"于","009","2017/5/8 14:13:43","1","","指纹","" ,"于","009","2017/5/8 16:10:01","1","","指纹","" ,"于","009","2017/5/8 16:57:31","1","","指纹","" ,"于","009","2017/5/8 18:26:59","1","","指纹","" ,"于","009","2017/5/8 18:57:02","1","","指纹","" ,"于","009","2017/5/8 18:58:06","1","","指纹","" ,"于","009","2017/5/9 8:42:26","1","","指纹","" ,"于","009","2017/5/9 9:34:07","1","","指纹","" ,"于","009","2017/5/9 11:46:12","1","","指纹","" ,"于","009","2017/5/9 13:47:02","1","","指纹","" ,"于","009","2017/5/9 17:25:17","1","","指纹","" ,"于","009","2017/5/9 18:05:02","1","","指纹","" ,"于","009","2017/5/10 8:46:50","1","","指纹","" ,"于","009","2017/5/10 10:14:49","1","","指纹","" ,"于","009","2017/5/10 10:37:15","1","","指纹","" ,"于","009","2017/5/10 11:35:05","1","","指纹","" ,"于","009","2017/5/10 13:34:00","1","","指纹","" ,"于","009","2017/5/10 15:28:08","1","","指纹","" ,"于","009","2017/5/10 16:13:17","1","","指纹","" ,"于","009","2017/5/10 18:11:05","1","","指纹","" ,"于","009","2017/5/11 8:50:41","1","","指纹","" ,"于","009","2017/5/11 9:57:43","1","","指纹","" ,"于","009","2017/5/11 10:18:26","1","","指纹","" ,"于","009","2017/5/11 11:25:47","1","","指纹","" ,"于","009","2017/5/11 13:49:23","1","","指纹","" ,"于","009","2017/5/11 14:02:04","1","","指纹","" ,"于","009","2017/5/11 16:58:33","1","","指纹","" ,"于","009","2017/5/11 18:23:28","1","","指纹","" ,"于","009","2017/5/12 8:49:37","1","","指纹","" ,"于","009","2017/5/12 11:27:42","1","","指纹","" ,"于","009","2017/5/12 13:47:13","1","","指纹","" ,"于","009","2017/5/12 14:10:01","1","","指纹","" ,"于","009","2017/5/12 15:52:46","1","","指纹","" ,"于","009","2017/5/12 17:30:29","1","","指纹","" ,"于","009","2017/5/12 18:09:34","1","","指纹","" ,"于","009","2017/5/15 8:50:10","1","","指纹","" ,"于","009","2017/5/15 13:38:37","1","","指纹",""