对于一道mysql查询面试题的思索解决历程-百分百源码网

对于一道mysql查询面试题的思索解决历程

发布时间：09/01 来源：未知浏览：关键词：

前几天面试，笔试时碰到这道题，读了几遍问题都是懵懵懂懂，“一个段工夫内至少N天，这N天中天天的分数总和要大于M”，好绕，最后没有写对。 啰嗦（可跳过）

前几天面试，笔试时碰到这道题，读了几遍问题都是懵懵懂懂，“一个段工夫内至少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` >= '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查询面试题的思索解决历程的细致内容，更多请关注百分百源码网其它相干文章！

打赏