SQL如何按列分区 从指定分组中各抽取固定数量随机行

需求背景 #

现有表结构及测试数据如下：

需要实现的逻辑：

• 先过滤出Country取值为a、c的记录
• 按Country分组，每个分组随机返回2条记录

预期返回结果示例：

具体实现 #

核心逻辑是先给每个国家分组内的记录做随机排序，再截取每个分组的前2条，不同数据库环境的写法如下：

• 支持窗口函数的环境（MySQL 8.0及以上、PostgreSQL 8.4及以上、SQL Server、Oracle等）
  直接使用ROW_NUMBER()窗口函数实现，写法最简洁：

  SELECT Country, id
  FROM (
      SELECT
          Country,
          id,
          ROW_NUMBER() OVER (
              PARTITION BY Country
              ORDER BY RAND()
          ) AS row_num
      FROM 你的业务表名
      WHERE Country IN ('a', 'c')
  ) tmp
  WHERE row_num <= 2;
  

  不同数据库的随机函数替换提示：SQL Server把RAND()换成NEWID()，Oracle把RAND()换成DBMS_RANDOM.VALUE即可。

• 不支持窗口函数的低版本MySQL（5.x版本）
  用用户变量模拟分区行号计算逻辑：

  SELECT Country, id
  FROM (
      SELECT
          Country,
          id,
          @row_num := IF(@current_grp = Country, @row_num + 1, 1) AS row_num,
          @current_grp := Country
      FROM (
          -- 先把目标数据按分组打散排序
          SELECT * FROM 你的业务表名 
          WHERE Country IN ('a', 'c')
          ORDER BY Country, RAND()
      ) sorted_data, (SELECT @row_num := 0, @current_grp := '') AS init_vars
  ) tmp
  WHERE row_num <= 2;
  

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