SQL分組查詢後取每組的前N條記錄

來自公眾號：程式亦非猿

本文由 Leon 同學授權釋出

另外大家不要覺得自己寫不好，不用怕，我可以指導你，Leon 同學在我指導下就改了幾版，進步非常大，寫文章既能讓自己加深印象又能幫助別人，何樂不為呢？

一、前言

分組查詢是常見的SQL查詢陳述句。首先，我們知道MySQL資料庫分組功能主要是透過GROUP BY關鍵字來實現的，而且GROUP BY通常得配合聚合函式來使用用，比如說分組之後你可以計數（COUNT），求和（SUM），求平均數（AVG）等。但是今天我們要探討的不是GROUP BY關鍵字學習和使用，而是一種有點另類的“分組”查詢。

最近，專案上遇到這樣一個功能需求。系統中存在資訊資訊這樣一個功能模組，用於釋出一些和業務相關的活動動態，其中每條資訊資訊都有一個所屬型別（如科技類的資訊、娛樂類、軍事類···）和瀏覽量欄位。

而業務系統的官網上需要滾動展示一些熱門資訊資訊串列（瀏覽量越大代表越熱門），而且每個類別的相關資訊記錄至多顯示3條，換句話：“按照資訊分類分組，取每組的前3條資訊資訊串列”。

後面在嘗試 GROUP BY 使用的各種方式都不能實現，最後在查閱相關資料後找到了實現的解決方法。

下麵，我將模擬一些實際的測試資料重現問題的解決過程。

一、資料準備

資料庫: MySQL 8.0社群版

表設計

• 資訊分類表:

• 資訊資訊記錄表:

初始化SQL陳述句:

 1DROP TABLE IF EXISTS `info`;
 2CREATE TABLE `info`  (
 3  `id` int(11) NOT NULL AUTO_INCREMENT,
 4  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 5  `views` int(255) DEFAULT NULL,
 6  `info_type_id` int(11) DEFAULT NULL,
 7  PRIMARY KEY (`id`) USING BTREE
 8) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 9
10-- ----------------------------
11-- Records of info
12-- ----------------------------
13INSERT INTO `info` VALUES (1, '中日海軍演習', 10, 4);
14INSERT INTO `info` VALUES (2, '美俄軍事競賽', 22, 4);
15INSERT INTO `info` VALUES (3, '流浪地球電影大火', 188, 1);
16INSERT INTO `info` VALUES (4, '葛優癱', 99, 2);
17INSERT INTO `info` VALUES (5, '周傑倫出軌了', 877, 2);
18INSERT INTO `info` VALUES (6, '蔡依林西安演唱會', 86, 1);
19INSERT INTO `info` VALUES (7, '中紀委調鹽', 67, 3);
20INSERT INTO `info` VALUES (8, '人民大會堂', 109, 3);
21INSERT INTO `info` VALUES (9, '重慶稱為網紅城市', 202, 1);
22INSERT INTO `info` VALUES (10, '胡歌結婚了', 300, 2);
23INSERT INTO `info` VALUES (11, 'ipone15馬上上市', 678, 2);
24INSERT INTO `info` VALUES (12, '中國探月成功', 54, 4);
25INSERT INTO `info` VALUES (13, '釣魚島對峙', 67, 4);
26
27DROP TABLE IF EXISTS `info_type`;
28CREATE TABLE `info_type`  (
29  `id` int(11) NOT NULL AUTO_INCREMENT,
30  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
31  PRIMARY KEY (`id`) USING BTREE
32) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
33
34-- ----------------------------
35-- Records of info_type
36-- ----------------------------
37INSERT INTO `info_type` VALUES (1, '娛樂');
38INSERT INTO `info_type` VALUES (2, '八卦');
39INSERT INTO `info_type` VALUES (3, '政治');
40INSERT INTO `info_type` VALUES (4, '軍事');

資訊分類示例資料如下：

資訊資訊記錄表示例資料如下：

需求 ：取熱門的資訊資訊串列且每個類別隻取前3條。

二、核心思想

一般意義上我們在取前N條記錄時候，都是根據某個業務欄位進行降序排序，然後取前N條就能實現。

形如“select * from info order by views asc limit 0,3 ”，這條SQL就是取info表中的前3條記錄。

但是當你仔細閱讀我們的題目要求，你會發現：“它是讓你每個型別下都要取瀏覽量的前3條記錄”。

一種比較簡單但是粗暴的方式就是在Java程式碼中迴圈所有的資訊型別，取出每個型別的前3條記錄，最後進行彙總。雖然這種方式也能實現我們的要求，但存在很嚴重的弊端，有可能會傳送多次（誇張的說成百上千次也是有可能）sql陳述句，這種程式顯然是有重大缺陷的。

但是，我們換一種思路。我們想在查詢每條資訊記錄時要是能查出其所在型別的排名就好了，然後根據排名欄位進行過濾就好了。這時候我們就想到了子查詢，而且MySQL是可以實現這樣的功能子查詢的。要計算出某條資訊資訊的在同資訊分類下所有記錄中排第幾名，換成算出 有多少條瀏覽量比當前記錄的瀏覽量高，然後根據具體的多少（N）條+1就是N+1就是當前記錄所在其分類下的的排名。

假如以本文上面的示例資料說明：就是在計算每個資訊資訊記錄時，多計算出一列作為其“排名”欄位，然後取“排名”欄位的小於等於3的記錄即可。如果這裡還不是很理解的話，就先看下麵的SQL,然後根據SQL再回過頭來理解這段話。

三、SQL實現

方法一

SQL陳述句：

1     SELECT t.* from (
2         SELECT
3             t1.*,
4             (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) top
5         FROM
6         info t1
7     ) t where top <=3 order by t.info_type_id,top

查詢結果:

說明：
分析top欄位的子查詢，發現其滿足條件有兩個：其一是info_type_id和當前記錄的type_id相等；其二是info表所有記錄大於
當前記錄的瀏覽量且info_type_id相等的記錄數量（假設為N），所有N+1就等於當前記錄在其分類下的按照瀏覽量降序排名。

方法二

SQL陳述句：

1    SELECT
2        t1.*    
3    FROM
4    info t1
5    where (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) <=3
6    ORDER BY t1.info_type_id

查詢結果

說明: 方法二可以看做是方法一的變體。

方法三

SQL陳述句

1    SELECT
2        t1.*    
3    FROM
4    info t1
5    where exists (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views having (count(*) + 1) <= 3) 
6    ORDER BY t1.info_type_id

查詢結果:

四、小結

其實，有時候在面臨業務難題的時候，困難的地方往往不在技術本身，而在於我們解決問題的思維方式。

就正如案例中求記錄的所在分類的排名,把其對等的“轉換成有多少條同類別的記錄的瀏覽量比當前記錄的大（count聚合函式）”
問題馬上就迎刃而解了。