android - 从每个类别中至少选择一个？

SQLFiddle Link

我有一个包含一堆测试/考试问题的SQLite数据库。每个问题属于一个问题类别。

我的桌子看起来像这样:

目标
我想做的是选择5个随机问题，但结果必须包含，每个类别至少包含。目标是从每个类别中随机选择一组问题。

例如，输出可以是问题ID 1, 2, 5, 7, 8，2, 3, 6, 7, 8或8, 6, 3, 1, 7。

ORDER BY category_id，RANDOM()
通过执行下面的SQL，我可以从SQLite中随机获得问题列表，但是如何确保结果包含每个类别的问题呢？

基本上，我正在寻找类似SQLite版本的this的东西。

我只想得到5个结果，但每个类别一个(或多个)，所有类别都在结果集中表示。

赏金
添加了赏金，因为我很好奇是否有可能仅在SQLite中完成此操作。我可以在SQLite + Java中做到这一点，但是有没有办法仅在SQLite中做到这一点？ :)

SQLFiddle Link

最佳答案

答案的关键是结果中有两种类型的问题:对于每个类别，必须限制一个问题来自该类别；对于每个类别，必须限制一个问题。还有一些剩余的问题。

首先，受约束的问题:我们只从每个类别中选择一个记录:

SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r
FROM so_questions
GROUP BY category_id

(此查询依赖于SQLite 3.7.11中引入的功能(在Jelly Bean或更高版本中):在查询SELECT a, max(b)中，保证a的值来自具有最大b值的记录。)

我们还必须获取非约束问题(下一步将过滤掉约束集中已经存在的重复项):

SELECT id, category_id, question_text, 0 AS constrained, random() AS r
FROM so_questions

当我们将这两个查询与UNION组合在一起，然后按id分组时，我们会将所有重复项放在一起。然后选择max(constrained)可确保对于重复的组，仅保留受约束的问题(而所有其他问题每组仅具有一个记录)。

最后，ORDER BY子句确保约束的问题排在最前面，然后是一些随机的其他问题:

SELECT *, max(constrained)
FROM (SELECT id, category_id, question_text, 1 AS constrained, max(random()) AS r
      FROM so_questions
      GROUP BY category_id
      UNION ALL
      SELECT id, category_id, question_text, 0 AS constrained, random() AS r
      FROM so_questions)
GROUP BY id
ORDER BY constrained DESC, r
LIMIT 5

对于较早的SQLite / Android版本，我没有使用临时表就找不到解决方案(因为约束问题的子查询必须多次使用，但由于random()，它不能保持不变):

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE constrained AS
SELECT (SELECT id
        FROM so_questions
        WHERE category_id = cats.category_id
        ORDER BY random()
        LIMIT 1) AS id
FROM (SELECT DISTINCT category_id
      FROM so_questions) AS cats;

SELECT ids.id, category_id, question_text
FROM (SELECT id
      FROM (SELECT id, 1 AS c
            FROM constrained
            UNION ALL
            SELECT id, 0 AS c
            FROM so_questions
            WHERE id NOT IN (SELECT id FROM constrained))
      ORDER BY c DESC, random()
      LIMIT 5) AS ids
JOIN so_questions ON ids.id = so_questions.id;

DROP TABLE constrained;
COMMIT TRANSACTION;