前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【面试必备】这道MySQL面试题难住了90%的程序员。。。

【面试必备】这道MySQL面试题难住了90%的程序员。。。

作者头像
码农编程进阶笔记
发布2021-07-20 15:45:21
3640
发布2021-07-20 15:45:21
举报

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

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

uid int(11)

score int(11)

date date

1

2

2019-02-28

1

3

2019-03-02

3

2

2019-03-17

3

1

2019-03-17

3

2

2019-03-17

4

3

2019-03-25

3

5

2019-03-27

...

...

...

现在需要一份用户列表,这些用户在2019年3月份的31天中,至少要有16天,每天得分总和大于40分。使用一条sql语句表示。

思路

重新梳理需求,画出重点。

现在需要一份用户列表,这些用户在2019年3月份的31天中至少要有16天每天得分总和大于40分。使用一条sql语句表示。

用户列表 代表一个不重复的 uid 列表,可使用 DISTINCT uidGROUP BY uid 来实现。

在2019年3月份的31天中 使用 where 语句限定时间范围。

至少要有16天 需要对天 date 进行聚合,使用聚合函数 COUNT(*) > 15来进行判断。

(每人)每天得分总和大于40 需要对每天分数 score 分数进行聚合,使用聚合函数对 SUM(score) > 40来进行判断。

此处有2处聚合函数,但是是针对不同维度的(天和每天里的分数),所以需要使用子查询,将2处聚合分别放置在内外层的sql语句上。

由“从内到外”的原则,我们先对每天的得分进行聚合,那就是对天进行聚合。

代码语言:javascript
复制
-- 在2017年3月份的31天中
select * from scores where `date` >= '2019-03-01' and `date` <= '2019-03-31';


-- (每人)每天得分总和大于40
-- 使用 group by uid,date 实现对分数进行聚合,使用 having  sum() 过滤结果
select uid,date from scores where `date` >= '2019-03-01' and `date` <= '2019-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` >= '2019-03-01' and `date` <= '2019-03-31' group by uid, `date` having sum(score) > 40
) group by uid having count(*) > 15;

答案

代码语言:javascript
复制
SELECT uid FROM (
    SELECT uid,date FROM WHERE `date` >= '2019-03-01' AND `date` <= '2019-03-31' GROUP BY uid,`date` HAVING SUM(score) > 40
) WHERE GROUP BY uid HAVING count(*) > 15;

验证

代码语言:javascript
复制
-- 结构
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
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-11-06,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 码农编程进阶笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 思路
  • 答案
  • 验证
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档