mysql 评论文章面试题_一道mysql查询面试题的思考解决过程

啰嗦(可跳过)

前几天面试，笔试时遇到这道题，读了几遍题目都是懵懵懂懂，“一个段时间内至少N天，这N天中每天的分数总和要大于M”，好绕，最后没有写对。

今天想起来这道题，写出了答案并进行了sql语句的验证。

问题

某游戏使用mysql数据库，数据表 scores 记录用户得分历史，uid 代表用户ID， score 表示分数， date 表示日期，每个用户每天都会产生多条记录。

数据结构以及数据行如下：

uid int(11)

score int(11)

date date

1

2

2017-02-28

1

3

2017-03-02

3

2

2017-03-17

3

1

2017-03-17

3

2

2017-03-17

4

3

2017-03-25

3

5

2017-03-27

...

...

...

现在需要一份用户列表，这些用户在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