mysql按某字段值分类后查询每类前n条数据

问题描述

最近遇到一个需求，按表中某字段的值分类（注意不是group by分组），再以某字段的值排序，查看某组中的前n条数据。
注意，这里的分类，不能简单理解为group by分组。从问题描述来看，group by分组与查看前n条数据是矛盾的。此问题不能简单使用group by 、order by 、limit 解决。

问题场景

在网上搜索相关解答，发现了有很多类似场景：

1. 电商网站，商品有很多分类，想取出每个分类下面的前五条数据；
2. 按日期分类，取某天或某月的前n条数据；
3. 按学生姓名分类，取某学生的前n条数据；
   …

场景模拟

模拟上述问题场景三，创建学生表，并添加数据如下图：

---

解决方案

大致有以下三种解决方案，都可以解决这类问题

一、UNION 拼接

如果分类很多，则会非常麻烦（注意UNION和ORDER BY以及LIMIT一起使用时，要用括号将每条查询语句扩起来）

(SELECT * FROM score a WHERE a.student = '张三' ORDER BY a.id LIMIT 2)
UNION
(SELECT * FROM score a WHERE a.student = '李四' ORDER BY a.id LIMIT 2)
UNION
(SELECT * FROM score a WHERE a.student = '王二' ORDER BY a.id LIMIT 2)

二、GROUP_CONCAT（）和FIND_IN_SET（）函数

原理：子查询按分类字段分组后，使用聚合函数得到每组用逗号分隔的字段值，外层查询看数据的唯一字段值是否能在子查询得到的逗号分隔的字段值里。

需要利用表中的唯一字段，正序、倒序、添加其他条件都比较方便；
其中正序、倒序修改两处ORDER BY即可；
额外添加查询条件仅需在子查询中添加即可。

SELECT 
    a.*
FROM
    score AS a,
    (SELECT 
        GROUP_CONCAT(id ORDER BY id) AS ids
    FROM
        score
    GROUP BY student) AS b
WHERE
    FIND_IN_SET(a.id, b.ids) BETWEEN 1 AND 2
ORDER BY a.student ASC, a.id;

• GROUP_CONCAT（）函数得到每组用逗号分隔的列值，最大长度可以通过group_concat_max_len环境变量设置；
• FIND_IN_SET（）函数返回id在ids中的位置，不存在返回0；

三、使用变量

原理：
@mid用于判断是否是某类的第一条数据；
@row相当于计数器，用于判断是某类的第几条数据；
最内层子查询将两个变量拼接到原表每条数据后。外层子查询通过判断两个变量，给原表数据分类后加上编号。最外层查询依据编号取值。

SELECT
	a.*, b.rownum
FROM
	score a
LEFT JOIN (
	SELECT
		id,
		CASE
	WHEN @mid = student THEN
		@row :=@row + 1
	ELSE
		@row := 1
	END rownum,
	@mid := student mid
FROM
	(
		SELECT
			@row := 0 ,@mid := '',
			c.*
		FROM
			score c
	) t
ORDER BY
	student,
	score DESC
) b ON b.id = a.id
WHERE
	b.rownum <= 3;