技術(shù)干貨實(shí)戰(zhàn)（6）- MySQL分組查詢后如何獲取每組的前N條數(shù)據(jù)，你會(huì)嗎？

作者： 修羅debug
版權(quán)聲明：本文為博主原創(chuàng)文章，遵循 CC 4.0 by-sa 版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接和本聲明。


“分組查詢”可以說(shuō)是相當(dāng)常見(jiàn)的SQL查詢語(yǔ)句，對(duì)于MySQL數(shù)據(jù)庫(kù)而言，其實(shí)現(xiàn)分組查詢的關(guān)鍵字為GROUP BY，而在使用GROUP BY期間一般還會(huì)有其他的聚合函數(shù)配合使用，比如計(jì)數(shù)用的COUNT(*)，統(tǒng)計(jì)數(shù)值和用的SUM(*)，而本文要介紹的是另一種類(lèi)型的“分組查詢”，即分組查詢出來(lái)后再查詢出每一組的前N條數(shù)據(jù)。


為了方便諸位理解，還是直接舉一個(gè)實(shí)際的案例吧：存在兩個(gè)數(shù)據(jù)庫(kù)表，一個(gè)叫課程表course，另一個(gè)叫課程類(lèi)型表course_type，這兩個(gè)數(shù)據(jù)庫(kù)表的DDL（數(shù)據(jù)庫(kù)表字段定義）如下所示：

（1）課程類(lèi)型表：   

CREATE TABLE `course_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '類(lèi)型名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='課程類(lèi)型';


（2）課程信息表：

CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type_id` int(11) NOT NULL COMMENT '類(lèi)型id',
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '課程名稱(chēng)',
  `scan_total` int(255) DEFAULT NULL COMMENT '課程瀏覽量',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='課程信息表';


現(xiàn)在的功能需求是：找出每種課程類(lèi)型中課程瀏覽量排名前3的課程記錄,而這種場(chǎng)景經(jīng)?？梢栽谝恍┰诰€教育平臺(tái)中見(jiàn)到，如下圖所示為某個(gè)在線教育平臺(tái)中“后端開(kāi)發(fā)”這一大類(lèi)型里面每種小分類(lèi)的課程銷(xiāo)量排行榜：



接下來(lái)，我們將找尋各種方式去實(shí)現(xiàn)這一功能需求！

（1）傳統(tǒng)的實(shí)現(xiàn)方式一般是采用Java代碼的方式先查詢出每種課程類(lèi)型數(shù)據(jù)，然后再遍歷每一種課程類(lèi)型，在課程表中根據(jù)課程類(lèi)型匹配查詢出瀏覽量前3的課程數(shù)據(jù)，即 type_id=xx order by scan_total desc limit 3；


其代碼實(shí)現(xiàn)方式在這里就不貼出來(lái)了，感興趣的小伙伴可以自己動(dòng)手?jǐn)]一擼！

此種實(shí)現(xiàn)方式最終固然可以實(shí)現(xiàn)功能需求，但是，有一個(gè)不好的地方在于需要在遍歷每一種課程類(lèi)型時(shí)不斷發(fā)出查詢課程數(shù)據(jù)列表的SQL，如果課程類(lèi)型有10幾種，而每一種需要取幾十、甚至幾百條數(shù)據(jù)，那將耗費(fèi)很大的資源（建立數(shù)據(jù)庫(kù)鏈接是需要耗資源的：內(nèi)存、CPU、網(wǎng)絡(luò)、磁盤(pán)…）


（2）因此，我們轉(zhuǎn)而求其次，采用SQL查詢一次性來(lái)搞定！在進(jìn)行實(shí)操之前，debug建議諸位先打開(kāi)Navicat嘗試擼一擼，然后再回過(guò)頭來(lái)看看debug提供的實(shí)現(xiàn)方式.


為了實(shí)現(xiàn)這種功能，我們需要轉(zhuǎn)換下思考的角度：仔細(xì)閱讀需求，可以得知它是需要我們查找出每種課程類(lèi)型下課程瀏覽量前3的課程列表，其實(shí)就是找出每個(gè)課程在同種課程類(lèi)型下的瀏覽量排名，最后再找出每種課程類(lèi)型下排名前3的課程！


如下圖所示為課程信息表，其中，最后一列為debug自己計(jì)算出的每個(gè)課程在同種課程類(lèi)型下課程列表中的排名，即top值：


 

朝著上圖這個(gè)方向努力了，我們擼出了相應(yīng)的SQL，如下所示：

SELECT
	a.id,
	a.type_id,
	c.`name` AS typeName,
	a.scan_total,
	a.`name`,
	(
		SELECT
			COUNT(b.id) 
		FROM
			course AS b
		WHERE
			b.type_id = a.type_id
		AND b.scan_total > a.scan_total
	) AS top
FROM
	course AS a
LEFT JOIN course_type AS c ON c.id = a.type_id
ORDER BY
	a.type_id ASC,
	a.scan_total DESC;


執(zhí)行上述SQL后得到的結(jié)果如下圖所示：



（3）到這里我們已經(jīng)將每種類(lèi)型下每個(gè)課程的排名top計(jì)算出來(lái)了，需要注意的是，在上圖得到的結(jié)果中，因?yàn)?/span>Count(b.id) 得到的值可能為 0 ，因此0代表的就是第 1 名；

可能有些小伙伴還有些疑惑，為什么加個(gè)子查詢就可以得到上圖中的結(jié)果呢？其執(zhí)行過(guò)程是怎么樣的呢？OK，一圖以蔽之，直接看下圖相信就可以解答你心中的疑惑了：


 

（4）最后是直接在外層嵌一個(gè)大的查詢，然后取排名值 top < 3 的數(shù)據(jù)列表，即可以得到實(shí)現(xiàn)功能需求，其完整的SQL如下所示：   

SELECT
	t.*
FROM
	(
		SELECT
			a.id,
			a.type_id,
			c.`name` AS typeName,
			a.scan_total,
			a.`name`,
			(
				SELECT
					COUNT(b.id)
				FROM
					course AS b
				WHERE
					b.type_id = a.type_id
				AND b.scan_total > a.scan_total
			) AS top
		FROM
			course AS a
		LEFT JOIN course_type AS c ON c.id = a.type_id
		ORDER BY
			a.type_id ASC,
			a.scan_total DESC
	) AS t
WHERE
	t.top < 3


執(zhí)行上述SQL后即可以得到相應(yīng)的結(jié)果，如下圖所示：


至此，我們已經(jīng)完成了本文開(kāi)頭提出來(lái)的功能需求；那……還有沒(méi)有其他的實(shí)現(xiàn)方式呢？當(dāng)然有，只不過(guò)其實(shí)現(xiàn)起來(lái)雖然不同，但是其本質(zhì)思想跟本文開(kāi)頭debug提到的那樣“計(jì)算出排名top值”是差不多的；


諾，這就是另外的實(shí)現(xiàn)方式，從SQL語(yǔ)句就可以看出來(lái)，它是上述第一種實(shí)現(xiàn)方式的變形：   

SELECT
	t.*
FROM
	course AS t
WHERE
	(
		SELECT
			COUNT(*)
		FROM
			course AS c
		WHERE
			c.type_id = t.type_id
		AND c.scan_total > t.scan_total
	) < 3
ORDER BY
	t.type_id ASC,
	t.scan_total DESC


OK，本文講解到此介紹，打完收工，咱們下期再見(jiàn)！


總結(jié)

（1）代碼/數(shù)據(jù)庫(kù)下載：本文涉及到的數(shù)據(jù)庫(kù)建表語(yǔ)句DDL以及對(duì)應(yīng)的測(cè)試數(shù)據(jù)案例 可以通過(guò)關(guān)注微信公眾號(hào)：程序員實(shí)戰(zhàn)基地（掃描下圖微信公眾號(hào)即可），回復(fù)數(shù)字： 1001 ，即可下載 ！