sql 如何查出状态为迟到早退的人

• Z时代
• 2024-01-10
• 分类：技术分享

我要查出来 一月范围中 迟到早退的人数,一天中迟到，早退 才算是 迟到早退

CREATE TABLE `oa_attendance`  (
`id` int(11) NOT NULL AUTO_INCREMENT,
`useraccount` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '员工账号',
`signtype` tinyint(1) NOT NULL COMMENT '1--上班签到，2--下班签退',
`signdate` date NOT NULL COMMENT '考勤日期',
`signtime` time(0) NOT NULL COMMENT '考勤时间',
`status` tinyint(1) UNSIGNED ZEROFILL NOT NULL COMMENT '0--正常,1--迟到，2--早退，3--旷工',
`creatdate` datetime(0) NOT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 177 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of oa_attendance
-- ----------------------------
INSERT INTO `oa_attendance` VALUES (165, 'test1', 1, '2020-09-01', '08:34:00', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (166, 'test1', 2, '2020-09-01', '18:34:00', 0, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (167, 'test1', 1, '2020-09-02', '09:34:00', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (168, 'test1', 2, '2020-09-02', '18:34:00', 0, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (169, 'test1', 1, '2020-09-03', '19:34:00', 3, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (170, 'test1', 2, '2020-09-03', '19:34:00', 0, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (171, 'test1', 1, '2020-09-10', '09:48:13', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (172, 'test1', 2, '2020-09-10', '13:48:13', 2, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (173, 'test2', 1, '2020-09-01', '09:34:00', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (174, 'test2', 2, '2020-09-01', '14:34:00', 2, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (175, 'test2', 1, '2020-09-02', '08:34:00', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_attendance` VALUES (176, 'test2', 2, '2020-09-02', '08:34:00', 3, '2020-09-11 15:00:28');

回答

select signdate, useraccount
from oa_attendance 
where status in (1, 2)
group by signdate, useraccount
having count(*) = 2

以上是 sql 如何查出状态为迟到早退的人 的全部内容， 来源链接： utcz.com/a/43419.html