MySQL多次分組取前幾條的實現方法

MySQL支持使用嵌套子查詢或窗口函數的方式來實現多次分組取前幾條數據的需求。以下將分別介紹這兩種方法。

嵌套子查詢

嵌套子查詢可以通過使用LIMIT關鍵字以及ORDER BY排序來實現多次分組取前幾條的需求。示例如下：

SELECT t1.name AS school, t2.name AS major, t3.name AS student, t3.score FROM school AS t1 INNER JOIN major AS t2 ON t1.id = t2.school_id INNER JOIN student AS t3 ON t2.id = t3.major_id WHERE t3.score >= (SELECT MIN(score) FROM (SELECT DISTINCT score FROM student ORDER BY score DESC LIMIT 10) AS t) ORDER BY t1.id, t2.id, t3.score DESC LIMIT 100;

窗口函數

窗口函數可以通過使用ROW_NUMBER()函數和PARTITION BY子句來實現多次分組取前幾條的需求。示例如下：

SELECT t1.name AS school, t2.name AS major, t3.name AS student, t3.score FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY major_id ORDER BY score DESC) AS row_num FROM student) AS t3 INNER JOIN major AS t2 ON t3.major_id = t2.id INNER JOIN school AS t1 ON t2.school_id = t1.id WHERE t3.row_num<= 10 ORDER BY t1.id, t2.id, t3.score DESC LIMIT 100;