暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 【面试必备】这道MySQL面试题难住了90%的程序员。。。

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

码农编程进阶笔记 2019-11-06
441

点击上方“码农编程进阶笔记”，选择“置顶或者星标”

优质文章第一时间送达！

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

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

uid int(11)score int(11)date date
122019-02-28
132019-03-02
322019-03-17
312019-03-17
322019-03-17
432019-03-25
352019-03-27
.........

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

思路

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

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

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

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

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

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

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

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

    -- 在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;



    答案

      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;

      验证

        -- 结构
        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
        最后修改时间：2019-11-24 18:03:24
        文章转载自码农编程进阶笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

        评论