歡迎光臨
每天分享高質量文章

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

來自公眾號:程式亦非猿

本文由 Leon 同學授權釋出

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

一、前言

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

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

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

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

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

一、資料準備

資料庫: MySQL 8.0社群版

表設計

  • 資訊分類表:
id 主鍵
name 分類名稱
  • 資訊資訊記錄表:
code 說明
id 主鍵
title 資訊名稱
views 瀏覽量
info_type_id 資訊類別

初始化SQL陳述句:

 1DROP TABLE IF EXISTS `info`;
 2CREATE TABLE `info`  (
 3  `id` int(11NOT NULL AUTO_INCREMENT,
 4  `title` varchar(255CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
 5  `views` int(255DEFAULT NULL,
 6  `info_type_id` int(11DEFAULT NULL,
 7  PRIMARY KEY (`id`USING BTREE
 8ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
 9
10-- ----------------------------
11-- Records of info
12-- ----------------------------
13INSERT INTO `info` VALUES (1'中日海軍演習'104);
14INSERT INTO `info` VALUES (2'美俄軍事競賽'224);
15INSERT INTO `info` VALUES (3'流浪地球電影大火'1881);
16INSERT INTO `info` VALUES (4'葛優癱'992);
17INSERT INTO `info` VALUES (5'周傑倫出軌了'8772);
18INSERT INTO `info` VALUES (6'蔡依林西安演唱會'861);
19INSERT INTO `info` VALUES (7'中紀委調鹽'673);
20INSERT INTO `info` VALUES (8'人民大會堂'1093);
21INSERT INTO `info` VALUES (9'重慶稱為網紅城市'2021);
22INSERT INTO `info` VALUES (10'胡歌結婚了'3002);
23INSERT INTO `info` VALUES (11'ipone15馬上上市'6782);
24INSERT INTO `info` VALUES (12'中國探月成功'544);
25INSERT INTO `info` VALUES (13'釣魚島對峙'674);
26
27DROP TABLE IF EXISTS `info_type`;
28CREATE TABLE `info_type`  (
29  `id` int(11NOT NULL AUTO_INCREMENT,
30  `name` varchar(255CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
31  PRIMARY KEY (`id`USING BTREE
32ENGINE = 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) <= 36    ORDER BY t1.info_type_id

查詢結果:

四、小結

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

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


已同步到看一看
贊(0)

分享創造快樂