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

Question

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

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

Answer 1

数据库关系除法，having 子句实现：

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