我想要统计taken表里面的每位用户最近一天的答题数量
补充样本数据和表,数据库系统软件mysql5.7
CREATE TABLE `taken` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NULL DEFAULT NULL,
`createtime` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 23 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Fixed;
-- ----------------------------
-- Records of taken
-- ----------------------------
INSERT INTO `taken` VALUES (1, 1, 1633075836);
INSERT INTO `taken` VALUES (2, 1, 1633075836);
INSERT INTO `taken` VALUES (3, 1, 1633075836);
INSERT INTO `taken` VALUES (4, 1, 1633075836);
INSERT INTO `taken` VALUES (5, 1, 1633075836);
INSERT INTO `taken` VALUES (6, 1, 1633075836);
INSERT INTO `taken` VALUES (7, 1, 1633162236);
INSERT INTO `taken` VALUES (8, 2, 1633162236);
INSERT INTO `taken` VALUES (9, 2, 1633162236);
INSERT INTO `taken` VALUES (10, 2, 1633075836);
INSERT INTO `taken` VALUES (11, 2, 1633075836);
INSERT INTO `taken` VALUES (12, 2, 1633075836);
INSERT INTO `taken` VALUES (13, 2, 1633075836);
INSERT INTO `taken` VALUES (14, 3, 1633162236);
INSERT INTO `taken` VALUES (15, 3, 1633162236);
INSERT INTO `taken` VALUES (16, 3, 1633075836);
INSERT INTO `taken` VALUES (17, 3, 1633075836);
INSERT INTO `taken` VALUES (18, 3, 1633075836);
INSERT INTO `taken` VALUES (19, 1, 1633075836);
INSERT INTO `taken` VALUES (20, 1, 1633075836);
INSERT INTO `taken` VALUES (21, 2, 1633075836);
INSERT INTO `taken` VALUES (22, 2, 1633075836);
这是taken表,里面一条记录代表一个答题记录,user_id是用户id
我的尝试和结果
sql:
select count(1) as num,user_id,FROM_UNIXTIME(createtime,'%Y-%m-%d') as time from taken group by user_id,time
结果如下:
我想要的结果:
我只想要日期最近的记录,但user_id和time已经用在group by上,所以不能在time上加max()。
请问大神有没有其他更好的解法,先提前说句谢谢
推荐文章: