作者:跟-着感觉走 | 来源:互联网 | 2020-09-08 14:24
啰嗦(可跳过)前几天面试,笔试时遇到这道题,读了几遍题目都是懵懵懂懂,“一个段时间内至少N天,这N天中每天的分数总和要大于M”,好绕,最后没有写对。
今天想起来这道题,写出了答案并进行了sql语句的验证。
推荐:《mysql视频教程》
问题
某游戏使用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` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39;;
-- (每人)每天得分总和大于40
-- 使用 group by uid,date 实现对分数进行聚合,使用 having sum() 过滤结果
select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40;
-- 至少要有16天
-- 以上条结果为基础,在对 group by uid 实现对天进行聚合,使用 having count() 过滤结果
select uid from (
select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40
) group by uid having count(*) > 15;
答案
SELECT uid FROM (
SELECT uid,date FROM WHERE `date` >= &#39;2017-03-01&#39; AND `date` <= &#39;2017-03-31&#39; 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 (&#39;1&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;2&#39;, &#39;1&#39;, &#39;2&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;3&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;11&#39;, &#39;1&#39;, &#39;4&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;12&#39;, &#39;1&#39;, &#39;3&#39;, &#39;2018-04-06&#39;);
INSERT INTO `scores` VALUES (&#39;4&#39;, &#39;1&#39;, &#39;3&#39;, &#39;2018-04-07&#39;);
INSERT INTO `scores` VALUES (&#39;5&#39;, &#39;2&#39;, &#39;2&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;6&#39;, &#39;2&#39;, &#39;4&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;7&#39;, &#39;2&#39;, &#39;1&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;8&#39;, &#39;3&#39;, &#39;3&#39;, &#39;2018-04-06&#39;);
INSERT INTO `scores` VALUES (&#39;9&#39;, &#39;3&#39;, &#39;1&#39;, &#39;2018-04-05&#39;);
INSERT INTO `scores` VALUES (&#39;10&#39;, &#39;3&#39;, &#39;2&#39;, &#39;2018-04-04&#39;);
-- 因为数据录入量有限,我们将结果改为修改改为:
-- 获取一个用户列表,时间范围是4号到6号,至少要有2天,每天分数总和大于2。
-- 查询
-- 非最精简语句,包含调试语句,可分段运行查看各个语句部分的效果。
SELECT
uid
FROM
(
SELECT
uid,
`date`,
sum(score) AS total_score
FROM
scores
WHERE
`date` > &#39;2018-04-03&#39;
AND `date` <&#39;2018-04-07&#39;
GROUP BY
uid,
`date`
HAVING
total_score > 2
ORDER BY
uid,
date
) AS a
GROUP BY
uid
HAVING
count(*) > 1;
-- 答案是:
uid : 1
以上就是关于一道mysql查询面试题的思考解决过程的详细内容,更多请关注 第一PHP社区 其它相关文章!