SQLite如何实现查询table_a每个分类对应table_b的1条随机数据

方案1：窗口函数实现（SQLite 3.25.0+ 推荐） #

这是性能最优、逻辑最清晰的实现方式，借助SQLite内置的RANDOM()随机函数和窗口函数实现分组随机取数：

SELECT 
  a.category_id,
  a.category,
  b.word,
  b.word_category,
  b.word_id
FROM table_a a
JOIN (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY RANDOM()) AS rn
  FROM table_b
) b ON a.category_id = b.category_id AND b.rn = 1;

逻辑说明：子查询会为table_b中每个category_id下的所有记录随机排序并生成行号，筛选行号为1的记录即为每个分类的随机一条词汇，再和分类表table_a关联即可得到完整结果。

方案2：低版本SQLite兼容实现 #

如果你使用的SQLite版本低于3.25.0不支持窗口函数，可以用相关子查询实现：

SELECT 
  a.category_id,
  a.category,
  b.word,
  b.word_category,
  b.word_id
FROM table_a a
JOIN table_b b ON a.category_id = b.category_id
WHERE b.word_id = (
  SELECT word_id 
  FROM table_b 
  WHERE category_id = a.category_id 
  ORDER BY RANDOM() 
  LIMIT 1
);

逻辑说明：对每个分类，子查询会随机取出该分类下的一个word_id，再匹配到table_b中对应的完整词汇记录。

注意事项 #

• 两种方案每次执行都会返回不同的随机匹配结果，完全符合随机取数的需求
• 方案1的性能更优，尤其当table_b数据量较大时，窗口函数的执行效率远高于相关子查询
• 条件允许的情况下优先升级SQLite版本使用方案1

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