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

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

CREATE TABLE `oa_attendance`  (
`id` int(11) NOT NULL AUTO_INCREMENT,
`useraccount` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '员工账号',
`sign % B @type` tinyint(1) NOT NULL COMMENT '1--上班签到,2--下班签退',
`signdate` date NOT NULL COMMENT '考勤日期',
`signtime` time(0) NOT NULL COMMENT '考勤时间',
`status` te * 1 F `inyint(1) UNSIGNED ZEROFILL NOT NULL COMMENT '0--正常,1--迟到,2--早退,3--旷工',
`creatdate` datetime(0) NOT NULL COMMENT '创建时间',
PRI& ! 7 O YMARY KEY (`id`) USING BTREE
) ENGIX Z z g s V 0NE = InnoDB AUTO_INCREMENT = 177 CHARACTER SET = utf8mb4 COLLATE = utfb v  e 08mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Recor$ = X | ? 9 l v ?ds of oa_attendance
-- ----------------------------
INSERT INTO `oa_attendance` VALUES (165, 'test1', 1, '2W T q020-09-01', '08:34:00', 1, '2020-09-11 15:00:28');
INSERTu G E INTO `oa_attendance` VA6 $ ] r i 3 s DLUES (166, 'test1', 2, '2020-09-01', '18:34:00', 0, '2020-09-11 15:00:28C 7 5 l');
INS l q #SERT INTO `oa_attendano H  f 1 Yce` 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- !  d q', 0, '2020-09-11 15:00:28');
INSF q v 9 ~ gERT INTO `oa_attendance` VALUES (169, 'test1& z 5 * & | f o', 1, '2020-09-03', '19:34:00', 3, '2020-09-11 15:00:28');
INSERT INTO* t x ] a B w `oa_attendanY w F m + u Tce` VAL. T g pUES (170, 'test1', 2, '2020-09-03', '19:34:00', 0, '2020-09-11 15:00:28');
INSERT INTO `o { e ^ B N Ra_attendance` VALUES (171, 'test1', 1, '2020-09-10', '09:48:13', 1, '2020-09-11 15:00:28');
INSERT INTO `oa_1 N ^ Dattendance` VALUES (172, 'test1', 2, '2020-09-10', '13:48:13', 2, '2020-09-11 15:00:28');
INSERT ID 7 N 3 n L [ d VNTO `oa_attendance` VALUES (173, 'test2', 1, s d F -'2020-09-01', '09:34:00',i d a 1, '2020-09-11 15:00:28');
INSERT INTO `oa_at[ K ? t ( k 1 Wtend- + ~ * ,ance` VALUES (I S X r E174, 'test2', 2, '2020-09-01', '14:34:00', 2F m X p P P | G , '2020-09-11 15:00:28');
INSERT INTO `oa_attendan& { H = [ce` VALUES (175, 'test2', 1, '2020-09-02', '08:34:00',$ W ( g ( A 2 l e 1, '2020-09-11 15:00:& V 528');
INSERT INT{ 7 | s p mO `oa_attendance` VALUES (R ) a176, 'test2', 2, '2020-09-02', '08:34:00', 3, '2020-09-11 15:00:28');

回答

数据库关系除法,having 子句实现:

select signdate, useraccouG = n 7nt
from oa z j_attendance 
where sta: L otus in (1, 2)
group by signdate,3 b | r X u : h useraccount
having count(*) = 2Z . Q w T