我想要统计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()。
请问大神有没有其他更好的解法,先提前说句谢谢

最佳答案
select count(*) as num,user_id,FROM_UNIXTIME(createtime,'%Y-%m-%d') as time
from taken pub
where FROM_UNIXTIME(createtime,'%Y-%m-%d')=(
  select FROM_UNIXTIME(createtime,'%Y-%m-%d') 
  from taken
  where user_id=pub.user_id
  order by createtime desc limit 1
)
group by user_id,time

也许是你要的

2周前 评论
renxiaotu (作者) 2周前
ammojs (楼主) 2周前
ammojs (楼主) 2周前
讨论数量: 4

你的最近时间有啥具体的条件依据。可以考虑having做条件筛选。

2周前 评论
ammojs (楼主) 2周前
KayuHo

二图子表再做一次用户分组,max 不就取最近最多的时间了吗

2周前 评论
ammojs (楼主) 2周前
select count(*) as num,user_id,FROM_UNIXTIME(createtime,'%Y-%m-%d') as time
from taken pub
where FROM_UNIXTIME(createtime,'%Y-%m-%d')=(
  select FROM_UNIXTIME(createtime,'%Y-%m-%d') 
  from taken
  where user_id=pub.user_id
  order by createtime desc limit 1
)
group by user_id,time

也许是你要的

2周前 评论
renxiaotu (作者) 2周前
ammojs (楼主) 2周前
ammojs (楼主) 2周前

@ammojs

file

file 这是用你的样本数据查的,第一个图是你的原语句,第二个图是我上面发的

2周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!