啰嗦（可跳過）
前幾天面試，筆試時遇到這道題，讀了幾遍題目都是懵懵懂懂，“一個段時間內至少N天，這N天中每天的分數總和要大于M”，好繞，最后沒有寫對。
今天想起來這道題，寫出了答案并進行了sql語句的驗證。
推薦：《mysql視頻教程》，《mysql視頻教程2020》
問題
某游戲使用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-01'?and?`date`??40; --?至少要有16天 --?以上條結果為基礎，在對?group?by?uid?實現對天進行聚合，使用?having??count()?過濾結果 select?uid?from?( ????select?uid,date?from?scores?where?`date`?>=?'2017-03-01'?and?`date`??40 )?group?by?uid?having?count(*)?>?15;
答案
SELECT?uid?FROM?( ????SELECT?uid,date?FROM?WHERE?`date`?>=?'2017-03-01'?AND?`date`??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`??2 ????????ORDER?BY ????????????uid, ????????????date ????)?AS?a GROUP?BY ????uid HAVING ????count(*)?>?1; --?答案是： uid?:?1
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
