从每个类别中至少选择一个？ [英] Select at least one from each category?

问题描述

SQLite版本。

我想获得只有5个结果，但一个（或更多）从每个类别，所有的类别表示在结果集。

赏金

添加了赏金，因为我很好奇是否可以在SQLite中完成此操作。我可以做到在SQLite + Java，但有没有办法在SQLite只这样做？ ：）

SQLFiddle链接

解决方案

答案的关键是结果中有两种问题： ，一个问题必须约束来自该类别;

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

  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）然后确保对于具有重复的组，只有受限问题保留（而所有其他问题每组只有一个记录） p>

最后， ORDER BY 子句确保受约束的问题先出现，然后是一些随机的其他问题：

  SELECT *，max（constrained）
 FROM（SELECT id，category_id，question_text，1 AS约束，max（random（））AS r 
 FROM so_questions 
 GROUP BY category_id 
 UNION ALL 
 SELECT id，category_id，question_text，0 AS constrained，random b $ b FROM so_questions）
 GROUP BY id 
 ORDER BY约束DESC，r 
 LIMIT 5 
  

---

对于早期的SQLite / Android版本，我没有找到一个不使用临时表的解决方案（因为受约束问题的子查询必须使用多个但是不会因为 random（）而保持不变：

  BEGIN TRANSACTION; 
 
 CREATE TEMPORARY TABLE约束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 id 
 JOIN so_questions ON ids.id = so_questions.id; 
 
 DROP TABLE约束; 
 COMMIT TRANSACTION; 
  

SQLFiddle Link

I've got an SQLite database with a bunch of test/exam questions. Each question belongs to one question category.

My table looks like this:

The goal
What I'm trying to do is select 5 random questions, but the result must contain at least one from each category. The goal is to select a random set of questions with questions from each category.

For example, the output could be question IDs 1, 2, 5, 7, 8, or 2, 3, 6, 7, 8 or 8, 6, 3, 1, 7.

ORDER BY category_id, RANDOM()
I can get a random list of questions from SQLite by executing the SQL below, but how would I make sure that the result contains a question from each of my categories?

Basically, I'm looking for something like this, the SQLite version.

I would like to get only 5 results, but one(or more) from each category, with all the categories represented in the result set.

Bounty
Added a bounty because I'm curious whether or not it is possible to accomplish this in SQLite only. I can do it in SQLite+Java, but is there a way to do this in SQLite only? :)

SQLFiddle Link

解决方案

The key to the answer is that there are two kinds of questions in the result: for each category, one question that must be constrained to come from that category; and some remaining questions.

First, the constrained questions: we just select one record from each category:

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

(This query relies on a feature introduced in SQLite 3.7.11 (in Jelly Bean or later): in a query SELECT a, max(b), the value of a is guaranteed to come from the record that has the maximum b value.)

We also have to get the non-constrained questions (filtering out the duplicates that are already in the constrained set will happen in the next step):

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

When we combine these two queries with UNION and then group by the id, we have all the duplicates together. Selecting max(constrained) then ensures that for the groups that have duplicates, only the constrained question remains (while all the other questions have only one record per group anyway).

Finally, the ORDER BY clause ensures that the constrained questions come first, followed by some random other questions:

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

---

For earlier SQLite/Android versions, I haven't found a solution without using a temporary table (because the subquery for the constrained question must be used multiple times, but does not stay constant because of the 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;

这篇关于从每个类别中至少选择一个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！