MYSQL从每个类别中选择2个随机行 [英] MYSQL select 2 random rows from each categories

问题描述

我有一个带有以下列的mysql表:

I have a mysql table with columns like this:

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

在此表中，我有2000个数据，并且有7个不同的类别".我想从此表中获取15个随机行，每个类别中的行数均相等.由于我有7个类别，因此不可能从每个类别中获得相等数量的行.在这种情况下，我可以从一个类别中获得3个.如何通过一个查询实现?

我当时想从每个类别中获取2行.然后，我总共将有14行，然后可以从表中获得1个随机行并合并记录.请给我看一个示例查询.谢谢.这是我的实际表结构:

In this table I have 2000 data and there are 7 different 'category'. I want to fetch 15 random rows from this table with equal number from each category. Since I have 7 categories, it's not possible to get equal number of rows from each category. In this case I can get 3 from one category. How can I achieve that with one query?

I was thinking of getting 2 rows from each categories. Then I will have 14 rows in total, and then I can get 1 random row from the table and merge records. Please show me an example query for this. Thanks. Here is my actual table structure:

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 ;

推荐答案

按照您所描述的每个类别仅获取2个，最后随机获取一个.它不是一个查询，而是一个结果集，可能是您需要的:

Just fetch 2 per category as you described, and one random at the end. It is not one query, but one result-set, which might be what you need:

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个随机问题.

(The nested Select allows you to sort by rand() per category) Nothing special so far - 2 random questions per category.

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

The tricky part now is to add the 15th, element WITHOUT selecting any of those you already have.

要通过一个"电话实现这一目标，您可以执行以下操作:

To achieve this with "one" call, You can do the following:

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

• 从此结果中选择所有内容，限制为0.15.

• Take the subset of 14 questions you have selected like above.
• Union this with a uncategorized set of random sorted things from the database. (limit 0,15)

• Select all from this result, limit 0,15.

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

IF the first 14 elements of the LAST subquery are already selected - they will be removed due to UNION, and a independent 15th element is guaranteed.

类似的东西:

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个问题.

This is somewhat ugly, but should exactly do what you need: 2 random questions from EACH category, and finally a random question that has NOT been selected already from ANY category. A total of 15 questions at any time.

(旁节点:您也可以运行第二个查询，在确定7个类别的14个问题之后，使用NOT IN ()拒绝已选择的问题.)

(Sidenode: You could as well run a second query, using NOT IN () to dissallow already selected questions after determining the 14 questions for the 7 categories.)

不幸的是，SQL Fiddle目前无法正常工作.这是一些小提琴代码:

Unfortunately SQL Fiddle is not working at the moment. Here's some fiddle code:

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个问题(最后一行)始终是类别中剩下的一个问题.

the example data contains 3 questions per type, leading to the result that the 15th question (last row) is ALWAYS the one remaining from a category.

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