搜尋

首頁 >資料庫 >mysql教程 >關於一道mysql查詢面試題的思考解決過程

關於一道mysql查詢面試題的思考解決過程

藏色散人
藏色散人轉載
2020-04-08 08:57:352944瀏覽

囉嗦（可跳過）

前幾天面試，筆試時遇到這題，讀了幾遍題目都是懵懵懂懂，「一個一段時間內至少N天，這N天中每天的分數總和要大於M”，好繞，最後沒有寫對。

今天想起來這題，寫出了答案並進行了sql語句的驗證。

推薦：《mysql影片教學》，《mysql面試題2020》

問題

某遊戲使用mysql資料庫，資料表scores 記錄使用者得分歷史，uid 代表使用者ID， score 表示分數， date 表示日期，每個使用者每天都會產生多筆記錄。

資料結構以及資料行如下：

關於一道mysql查詢面試題的思考解決過程

現在需要一份使用者列表，這些使用者在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中文網其他相關文章！

sql mysql count date 数据结构 数据库
陳述：
本文轉載於：segmentfault.com。如有侵權，請聯絡admin@php.cn刪除
上一篇：如何使用MySQL Sandbox快速部署mysql下一篇：如何使用MySQL Sandbox快速部署mysql

相關文章

看更多