Mysql京东的一道面试题目 比较综合

 查询sql ：求取一个月之内每天分数和大于40的天数大于1的人

# 凭借印象记下来的  一个意思
# 计算出每人每天的分数和  然后每人每天分数和要求大于40
# 然后统计一个月之内每人每天分数大于40的天数，要求一个月之内每人每天分数大于40的天数大于1
select id,count(id) num,sum(afterscore) res from (
select sum(score) as afterscore,id,time from score
where time between '2019-09-01' and '2019-09-30'
group by id,time having afterscore>40
) as temptable
group by temptable.id
having num>1;

表格与查询结果

建表sql 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(10) DEFAULT NULL,
  `score` varchar(11) DEFAULT NULL,
  `time` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '255', '2019-09-04');
INSERT INTO `score` VALUES ('1', '44', '2019-09-15');
INSERT INTO `score` VALUES ('2', '67', '2019-09-13');
INSERT INTO `score` VALUES ('3', '55', '2019-09-15');
INSERT INTO `score` VALUES ('3', '12', '2019-09-19');
INSERT INTO `score` VALUES ('3', '56', '2019-09-19');
INSERT INTO `score` VALUES ('3', '44', '2019-09-19');
INSERT INTO `score` VALUES ('4', '2', '2019-09-04');
INSERT INTO `score` VALUES ('4', '333', '2019-09-12');
INSERT INTO `score` VALUES ('4', '44', '2019-09-12');
INSERT INTO `score` VALUES ('4', '55555', '2019-09-13');
INSERT INTO `score` VALUES ('4', '444', '2019-06-19');