分享mysql查詢面試題的思考解決過程

今天小編給大家分享的是分享mysql查詢面試題的思考解決過程前幾天面試，筆試時(shí)遇到這道題，讀了幾遍題目都是懵懵懂懂，“一個(gè)段時(shí)間內(nèi)至少N天，這N天中每天的分?jǐn)?shù)總和要大于M”，好繞，最后沒有寫對(duì)。今天想起來這道題，寫出了答案并進(jìn)行了sql語句的驗(yàn)證。一起來看看吧。

問題

某游戲使用mysql數(shù)據(jù)庫(kù)，數(shù)據(jù)表 scores 記錄用戶得分歷史，uid 代表用戶ID， score 表示分?jǐn)?shù)， date 表示日期，每個(gè)用戶每天都會(huì)產(chǎn)生多條記錄。

數(shù)據(jù)結(jié)構(gòu)以及數(shù)據(jù)行如下：

現(xiàn)在需要一份用戶列表，這些用戶在2017年3月份的31天中，至少要有16天，每天得分總和大于40分。使用一條sql語句表示。

思路

重新梳理需求,畫出重點(diǎn)。

現(xiàn)在需要一份用戶列表，這些用戶在2017年3月份的31天中，至少要有16天，每天得分總和大于40分。使用一條sql語句表示。

用戶列表

代表一個(gè)不重復(fù)的 uid 列表，可使用 DISTINCT uid 或 GROUP BY uid 來實(shí)現(xiàn)。

在2017年3月份的31天中

使用 where 語句限定時(shí)間范圍。

至少要有16天

需要對(duì)天 date 進(jìn)行聚合，使用聚合函數(shù) COUNT(*) > 15來進(jìn)行判斷。

(每人)每天得分總和大于40

需要對(duì)每天分?jǐn)?shù) score 分?jǐn)?shù)進(jìn)行聚合，使用聚合函數(shù)對(duì) SUM(score) > 40來進(jìn)行判斷。

此處有2處聚合函數(shù)，但是是針對(duì)不同維度的（天和每天里的分?jǐn)?shù)），所以需要使用子查詢，將2處聚合分別放置在內(nèi)外層的sql語句上。

由“從內(nèi)到外”的原則，我們先對(duì)每天的得分進(jìn)行聚合，那就是對(duì)天進(jìn)行聚合。

-- 在2017年3月份的31天中
select * from scores where `date` >= '2017-03-01' and `date` <= '2017-03-31';
-- (每人)每天得分總和大于40
-- 使用 group by uid,date 實(shí)現(xiàn)對(duì)分?jǐn)?shù)進(jìn)行聚合，使用 having  sum() 過濾結(jié)果
select uid,date from scores where `date` >= '2017-03-01' and `date` <= '2017-03-31' group by uid, `date` having sum(score) > 40;
-- 至少要有16天
-- 以上條結(jié)果為基礎(chǔ)，在對(duì) group by uid 實(shí)現(xiàn)對(duì)天進(jìn)行聚合，使用 having  count() 過濾結(jié)果
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;

驗(yàn)證

-- 結(jié)構(gòu)
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;
-- 數(shù)據(jù)
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');
-- 因?yàn)閿?shù)據(jù)錄入量有限，我們將結(jié)果改為修改改為：
-- 獲取一個(gè)用戶列表，時(shí)間范圍是4號(hào)到6號(hào)，至少要有2天，每天分?jǐn)?shù)總和大于2。
-- 查詢
-- 非最精簡(jiǎn)語句，包含調(diào)試語句，可分段運(yùn)行查看各個(gè)語句部分的效果。
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