MYSQL 从每个类别中选择 2 个随机行

Question

我有一个包含如下列的 mysql 表:

`qid`, `category`, `question`, `choice_1`, `choice_2`, `choice_3`, `answer`

在这个表中我有 2000 条数据并且有 7 个不同的“类别”。我想从此表中获取 15 个随机行，每个类别的行数相等。因为我有 7 个类别，所以不可能从每个类别中获得相同数量的行。在这种情况下，我可以从一个类别中得到 3 个。如何通过一个查询实现这一目标？

我正在考虑从每个类别中获取 2 行。然后我总共会有14行，然后我可以从表中随机获取1行并合并记录。请给我看一个示例查询。谢谢。这是我的实际表结构:

CREATE TABLE IF NOT EXISTS `difi_questions` (
  `qid` smallint(6) NOT NULL AUTO_INCREMENT,
  `category` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `question` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `choice_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `choice_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `choice_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `answer` enum('1','2','3') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`qid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1959 ;

Answer 1

按照您的描述，每个类别只取 2 个，最后随机取一个。这不是一个查询，而是一个结果集，这可能是您需要的:

SELECT * FROM (SELECT * FROM questions WHERE category= 1 ORDER BY rand() limit 0,2) as t1
UNION
SELECT * FROM (SELECT * FROM questions WHERE category= 2 ORDER BY rand() limit 0,2) as t2
UNION 
SELECT * FROM (SELECT * FROM questions WHERE category= 3 ORDER BY rand() limit 0,2) as t3
UNION
...

(嵌套的 Select 允许您按类别按 rand() 排序)到目前为止没有什么特别的 - 每个类别 2 个随机问题。

现在棘手的部分是添加第 15 个元素，WITHOUT 选择您已经拥有的任何元素。

要通过“一次”调用实现此目的，您可以执行以下操作:

• 像上面那样选择 14 个问题的子集。
• 将其与数据库中随机排序的一组未分类的事物结合起来。 (限制为 0,15)

• 从此结果中选择全部，限制为 0,15。

• 如果 LAST 子查询的前 14 个元素已被选中 - 由于 UNION，它们将被删除，并且保证独立的第 15 个元素。

• 如果最终的内部查询也选择了 15 个不同的问题，则外部限制 0,15 只会将其中的第一个纳入结果。

类似于:

SELECT * FROM (
    SELECT * FROM (SELECT * FROM questions WHERE category= 1 ORDER BY rand() limit 0,2) as t1
    UNION
    SELECT * FROM (SELECT * FROM questions WHERE category= 2 ORDER BY rand() limit 0,2) as t2
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 3 ORDER BY rand() limit 0,2) as t3
    UNION
    ...
    UNION
    SELECT * FROM (SELECT * FROM questions ORDER BY rand() LIMIT 0,15) as t8
) AS tx LIMIT 0,15

这有点难看，但应该完全满足您的需要:每个类别中有 2 个随机问题，最后是一个尚未从任何类别中选择的随机问题。总共 15 个问题。

(旁节点:您还可以运行第二个查询，在确定 7 个类别的 14 个问题后，使用 NOT IN () 来禁止已经选择的问题。)

编辑:不幸的是，SQL Fiddle 目前不工作。这是一些 fiddle 代码:

CREATE TABLE questions (id int(10), category int(10), question varchar(20));

INSERT INTO questions (id, category, question)VALUES(1,1,"Q1");
INSERT INTO questions (id, category, question)VALUES(2,1,"Q2");
INSERT INTO questions (id, category, question)VALUES(3,1,"Q3");
INSERT INTO questions (id, category, question)VALUES(4,2,"Q4");
INSERT INTO questions (id, category, question)VALUES(5,2,"Q5");
INSERT INTO questions (id, category, question)VALUES(6,2,"Q6");
INSERT INTO questions (id, category, question)VALUES(7,3,"Q7");
INSERT INTO questions (id, category, question)VALUES(8,3,"Q8");
INSERT INTO questions (id, category, question)VALUES(9,3,"Q9");
INSERT INTO questions (id, category, question)VALUES(10,4,"Q10");
INSERT INTO questions (id, category, question)VALUES(11,4,"Q11");
INSERT INTO questions (id, category, question)VALUES(12,4,"Q12");
INSERT INTO questions (id, category, question)VALUES(13,5,"Q13");
INSERT INTO questions (id, category, question)VALUES(14,5,"Q14");
INSERT INTO questions (id, category, question)VALUES(15,5,"Q15");
INSERT INTO questions (id, category, question)VALUES(16,6,"Q16");
INSERT INTO questions (id, category, question)VALUES(17,6,"Q17");
INSERT INTO questions (id, category, question)VALUES(18,6,"Q18");
INSERT INTO questions (id, category, question)VALUES(19,7,"Q19");
INSERT INTO questions (id, category, question)VALUES(20,7,"Q20");
INSERT INTO questions (id, category, question)VALUES(21,7,"Q21");

查询

SELECT * FROM (
    SELECT * FROM (SELECT * FROM questions WHERE category= 1 ORDER BY rand() limit 0,2) as t1
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 2 ORDER BY rand() limit 0,2) as t2
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 3 ORDER BY rand() limit 0,2) as t3
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 4 ORDER BY rand() limit 0,2) as t4
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 5 ORDER BY rand() limit 0,2) as t5
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 6 ORDER BY rand() limit 0,2) as t6
    UNION 
    SELECT * FROM (SELECT * FROM questions WHERE category= 7 ORDER BY rand() limit 0,2) as t7
    UNION 
    SELECT * FROM (SELECT * FROM questions ORDER BY rand() LIMIT 0,15) as t8
) AS tx LIMIT 0,15

示例数据包含每种类型的 3 个问题，导致第 15 个问题(最后一行)始终是类别中剩余的问题。