MySQL 5.7中为每个val值随机选取2条数据的实现方案

问题背景 #

假设我们有如下结构的表my_table：

id val
--------
1 1
2 1
3 1
4 2
5 2
6 2
7 3
8 3
9 3

需要为每个val值随机选取2条包含id和val的记录，期望结果类似：

id val
--------
2 1
3 1
4 2
6 2
8 3
9 3

简洁可扩展的SQL方案 #

针对MySQL 5.7版本，这里有一个既简洁又能轻松扩展的方案——不管你是要每个分组选2条，还是25条都适用：

SELECT 
    val,
    SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY RAND() SEPARATOR ','), ',', 2) AS random_ids
FROM my_table
GROUP BY val;

方案说明

• GROUP_CONCAT(id ORDER BY RAND())：先按val分组，把每组内的id随机排序后拼接成逗号分隔的字符串
• SUBSTRING_INDEX(..., ',', 2)：从拼接好的字符串里截取前2个id，如果需要选25条，只需要把这里的2改成25就行

扩展：获取每行一条记录的格式

如果想要得到像示例里每行一条id+val的格式，可以结合交叉查询拆分字符串（MySQL 5.7没有原生的字符串拆分函数，我们用临时表来实现）：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.random_ids, ',', n.n), ',', -1) AS id,
    t.val
FROM (
    SELECT 
        val,
        GROUP_CONCAT(id ORDER BY RAND() SEPARATOR ',') AS random_ids
    FROM my_table
    GROUP BY val
) t
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 AS n -- 这里的数量对应要选的记录数，选25条就依次加到25
) n
WHERE n.n <= LENGTH(t.random_ids) - LENGTH(REPLACE(t.random_ids, ',', '')) + 1
ORDER BY t.val, n.n;

这个查询会把每个分组的随机id拆分成单独的行，完美匹配你想要的结果格式。

内容的提问来源于stack exchange，提问作者GuillaumeA