对于一道mysql查询面试题的思索解决历程
前几天面试,笔试时碰到这道题,读了几遍问题都是懵懵懂懂,“一个段时间内至少N天,这N天中每天的分数总和要大于M”,好绕,最后没有写对。
今天想起来这道题,写出了答案并停止了sql语句的验证。
引荐:《mysql视频教程》,《mysql面试题2020》
问题
某游戏使用mysql数据库,数据表 scores 记载会员得分历史,uid 代表会员ID, score 表示分数, date 表示日期,每个会员每天都会发生多笔记录。
数据构造乃至数据行如下:
此刻需要一份会员列表,这些会员在2017年3月份的31天中,至少要有16天,每天得分总和大于40分。使用一条sql语句表示。
思绪
从新梳理需求,画出重点。
此刻需要一份会员列表,这些会员在2017年3月份的31天中,至少要有16天,每天得分总和大于40分。使用一条sql语句表示。
会员列表
代表一个不反复的 uid 列表,可使用 DISTINCT uid 或 GROUP BY uid 来实现。
在2017年3月份的31天中
使用 where 语句限制时间范畴。
至少要有16天
需要对天 date 停止聚合,使用聚合函数 COUNT(*) > 15来停止推断。
(每人)每天得分总和大于40
需要对每天分数 score 分数停止聚合,使用聚合函数对 SUM(score) > 40来停止推断。
此处有2处聚合函数,但是是针对不一样维度的(天和每天里的分数),所以需要使用子查询,将2处聚合离别放置在里外层的sql语句上。
由“从内到外”的原则,我们先对每天的得分停止聚合,那就是对天停止聚合。
-- 在2017年3月份的31天中 select * from scores where `date` >= '2017-03-01' and `date` <= '2017-03-31'; -- (每人)每天得分总和大于40 -- 使用 group by uid,date 实现对分数停止聚合,使用 having sum() 过滤结果 select uid,date from scores where `date` >= '2017-03-01' and `date` <= '2017-03-31' group by uid, `date` having sum(score) > 40; -- 至少要有16天 -- 以上条结果为根基,在对 group by uid 实现对天停止聚合,使用 having count() 过滤结果 select uid from ( select uid,date from scores where `date` >= '2017-03-01' and `date` <= '2017-03-31' group by uid, `date` having sum(score) > 40 ) group by uid having count(*) > 15;
答案
SELECT uid FROM ( SELECT uid,date FROM WHERE `date` >= '2017-03-01' AND `date` <= '2017-03-31' GROUP BY uid,`date` HAVING SUM(score) > 40 ) WHERE GROUP BY uid HAVING count(*) > 15;
验证
-- 构造 CREATE TABLE `scores` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) DEFAULT NULL, `score` int(11) DEFAULT NULL, `date` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 数据 INSERT INTO `scores` VALUES ('1', '1', '1', '2018-04-03'); INSERT INTO `scores` VALUES ('2', '1', '2', '2018-04-03'); INSERT INTO `scores` VALUES ('3', '1', '1', '2018-04-04'); INSERT INTO `scores` VALUES ('11', '1', '4', '2018-04-04'); INSERT INTO `scores` VALUES ('12', '1', '3', '2018-04-06'); INSERT INTO `scores` VALUES ('4', '1', '3', '2018-04-07'); INSERT INTO `scores` VALUES ('5', '2', '2', '2018-04-04'); INSERT INTO `scores` VALUES ('6', '2', '4', '2018-04-04'); INSERT INTO `scores` VALUES ('7', '2', '1', '2018-04-03'); INSERT INTO `scores` VALUES ('8', '3', '3', '2018-04-06'); INSERT INTO `scores` VALUES ('9', '3', '1', '2018-04-05'); INSERT INTO `scores` VALUES ('10', '3', '2', '2018-04-04'); -- 由于数据录入量有限,我们将结果改为修改改为: -- 猎取一个会员列表,时间范畴是4号到6号,至少要有2天,每天分数总和大于2。 -- 查询 -- 非最精简语句,包括调试语句,可分段运转查看各个语句部分的结果。 SELECT uid FROM ( SELECT uid, `date`, sum(score) AS total_score FROM scores WHERE `date` > '2018-04-03' AND `date` < '2018-04-07' GROUP BY uid, `date` HAVING total_score > 2 ORDER BY uid, date ) AS a GROUP BY uid HAVING count(*) > 1; -- 答案是: uid : 1
以上就是关于一道mysql查询面试题的思索解决历程的具体内容,更多请关注百分百源码网其它相关文章!