如何从MySQL中的每个类别中获取随机记录？

Question

如何从MySQL中的每个类别中获取随机记录？

6

在我的MySQL数据库中，我有一个包含不同类别问题的表格。

我想编写一条SQL语句，返回每个类别中随机的3个问题。

这是数据库记录的示例：

id  question    category
1   Question A  1
2   Question B  1
3   Question C  1
4   Question D  1
5   Question D  1
6   Question F  2
7   Question G  2
8   Question H  2
9   Question I  2
10  Question J  2
11  Question K  3
12  Question L  3
13  Question M  3
14  Question N  3
15  Question O  3
16  Question P  3

以下是从上述列表中每个类别的所有问题中随机选择和混洗的3个结果输出：

2   Question B  1
4   Question D  1
3   Question C  1
10  Question J  2
7   Question G  2
9   Question I  2
11  Question K  3
15  Question P  3
13  Question M  3

我目前正在测试以下语句：

SELECT * FROM `random` ORDER BY RAND() LIMIT 0,3;

这只返回所有类别中的3个随机问题。

后来我查看了这个链接的示例： MYSQL select random of each of the categories

并尝试了以下内容：

(SELECT * FROM `random` WHERE category = 1 ORDER BY RAND() LIMIT 3)
UNION ALL
(SELECT * FROM `random` WHERE category = 2 ORDER BY RAND() LIMIT 3)
UNION ALL
(SELECT * FROM `random` WHERE category = 3 ORDER BY RAND() LIMIT 3)

但是我需要手动添加每个类别。

我的问题是：是否有可能自动获取所有类别的每个类别中的3条随机记录/行？

编辑

这不是问题的一部分，但是它会提供帮助。

虚拟数据创建者

查询代码将创建名为“random”的表，并创建名为“create_random”的存储过程。当您运行存储过程时，它将在随机表内创建虚拟数据。

DELIMITER $$
DROP TABLE IF EXISTS `random`;
DROP PROCEDURE IF EXISTS `create_random` $$

CREATE TABLE `random` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `question` VARCHAR(50) NULL DEFAULT NULL,
    `category` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=401
;

CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_random`()
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''

BEGIN

DECLARE v_max int unsigned DEFAULT 100;
DECLARE v_counter int unsigned DEFAULT 0;
DECLARE cat_counter int unsigned DEFAULT 0;

  TRUNCATE TABLE `random`;
  START TRANSACTION;
  WHILE v_counter < v_max DO
    IF v_counter %10=0 THEN SET cat_counter=cat_counter+1;
    END IF;
    INSERT INTO `random` (question, category) VALUES ( CONCAT('Question', FLOOR(0 + (RAND() * 65535))), cat_counter );
    SET v_counter=v_counter+1;
  END WHILE;
  COMMIT;
END

注意: 我尝试了所有的答案，都可以很好地运作。Gordon Linoff和pjanaway的回答是只从前三或后三个问题中随机选择，我检查了Gordon的答案，因为他第一个回答了，但这并不意味着其他的答案不好，它们都很好，由用户选择正确的答案或组合。我喜欢所有的答案并投票支持它们。Drew Pierce最新回答了这个问题，现在更有趣，几乎接近目标。感谢大家。

- Maytham Fahmi

如果问题中的单词也是随机的，那就真的很酷了。但说真的，看起来你已经完成了90%的工作。你是想让演示顺序有点混乱（即不是一个类别接着另一个类别）吗？还是最后一部分让我读了三遍：你想让所有的东西都自动化，而不是手动为每个新类别添加一个新联合体？可能是后者，抱歉。 - Drew

@DrewPierce，是的，想法是SQL语句应该打乱同一类别的问题，这样回答或获取问题的人不应该每次都期望得到相同的问题。这个例子很小，但会有许多不同类别的问题，想象一下如果我每周添加新类别，那么我就需要不断修改我的SQL语句，但如果我能以某种自动化的方式做到这一点，那将使我的工作更轻松;) 哈哈 - Maytham Fahmi

这个问题希望能够为您赢得一些应得的积分，因为它在许多场合都非常有用！ - Drew

如果有人从中受益，那会让我感到高兴。一开始，这似乎是一个非常容易实现的任务，但很快就变成了一个复杂的问题需要解决（对我来说可能如此）。我很高兴并感激社区中愿意帮助和贡献的人们，他们付出了时间和尊重。 - Maytham Fahmi

3个回答

5

是的，您可以通过枚举行并获取前三行来实现此操作：

select r.id, r.question, r.category
from (select r.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as seqnum
      from `random` r cross join
           (select @rn := 0, @c := -1) params
      order by category, rand()
     ) r
where seqnum <= 3;

- Gordon Linoff

你好Gordon。我在结果方面遇到了问题。最好是先阅读下面pj答案下的pastie，然后再看这个带有你内部查询的内容。http://pastie.org/10300277 - Drew

这里有一个小挑战。现在我已经建立了一个更大的随机数据库进行测试，当我运行你的代码和pjanaway的代码时，它们都选择三个问题并保持洗牌，但是当我再次运行代码时，它仍然选择相同的问题。我的想法是每个类别中有超过三个问题，应该从所有问题中选择3个并随机选择并打乱它们。在你的代码中，它只取前三个问题，而@pjanaway则取最后三个问题？ - Maytham Fahmi

1

@maytham . . . 如果代码返回相同的三个问题，则可能是随机数生成器的种子被重置了。你可以尝试手动设置它，看看是否会得到不同的结果（https://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html#function_rand）。 - Gordon Linoff

1

create schema so_gibberish; -- creates database 
use so_gibberish;   -- use it 

-- drop table random;   -- during debug
create table random 
(   id int auto_increment primary key,
    question varchar(50) not null,
    category int not null,
    randomOrder int not null,
    key (category)
);

-- drop table questions_for_user;   -- during debug
create table questions_for_user 
(   physcOrder int auto_increment primary key,
    id int not null,
    question varchar(50) not null,
    category int not null,
    randomOrder int not null,
    key (category)
);

创建一个存储过程来插入随机问题。每次调用该存储过程时，它会创建300个问题。

DELIMITER $$ 
drop procedure if exists createRandomQuestions$$ 
-- 17 categories of questions randomly created. yes random word questions and categories.

create procedure createRandomQuestions()
BEGIN
set @i=1;
WHILE @i<=300 DO
insert random (question,category) values ('xxx',1);
SELECT @lid:=LAST_INSERT_ID();  -- use id to seed, next 8 guaranteed different i think

UPDATE random SET question=concat(
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1), ' ?'
), category=floor(rand()*17+1),randomOrder=0
WHERE id=@lid;
set @i=@i+1;
END WHILE;
END;
$$
DELIMITER ;

叫它：

call createRandomQuestions();

检查：

select category,count(*) from random group by category order by category;
select count(*) from random;
select * from random limit 10;

现在在请求时为用户获取每个类别的三个随机问题。

设计一个良好的随机种子算法。以下是一个存根，最好使用外部源，如http到服务等。

update random set randomOrder=rand()*unix_timestamp();
truncate table questions_for_user;
insert into questions_for_user (id,question,category,randomOrder)
select id,question,category,randomOrder from random order by rand();

select r.* 
FROM questions_for_user r 
WHERE 
( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 
ORDER by r.category ASC, randomOrder;

+------------+-----+------------+----------+-------------+
| physcOrder | id  | question   | category | randomOrder |
+------------+-----+------------+----------+-------------+
|        297 | 266 | JNQH26DD ? |        1 |   841700408 |
|        247 | 286 | ORK15577 ? |        1 |   980764662 |
|         54 | 267 | T2HPRW88 ? |        1 |  1331420535 |
|        190 | 275 | U5BFCUFF ? |        2 |   836160445 |
|        192 | 285 | E3KDJ144 ? |        2 |  1166207975 |
|         55 | 293 | GFWQ0BEE ? |        2 |  1356597807 |
|        266 | 296 | 8MJCWR55 ? |        3 |  1121232849 |
|        173 | 288 | 6GGOI2CC ? |        3 |  1209081435 |
|        205 | 231 | LQMYMV44 ? |        3 |  1330946482 |
|        103 | 289 | FUODJHJJ ? |        4 |   392498036 |
|        274 | 295 | Y85VNBWW ? |        4 |   613800522 |
|        204 | 280 | 441X1YTT ? |        4 |  1141251557 |
|        162 | 273 | CC5FGKFF ? |        5 |    99041401 |
|         36 | 252 | Y7V58ZEE ? |        5 |  1124240820 |
|        143 | 234 | DWZULKBB ? |        5 |  1286225785 |
|         15 | 276 | 3J1KGJZZ ? |        6 |    51285374 |
|        265 | 294 | PTEMZ7GG ? |        6 |   649256602 |
|         91 | 271 | TNG23R11 ? |        6 |  1393790410 |
|         90 | 277 | DXE1W0GG ? |        7 |   622567356 |
|         20 | 274 | LQTFS7RR ? |        7 |  1376505632 |
|        121 | 279 | VQN6TVSS ? |        7 |  1428714367 |
|        268 | 265 | A99UU1YY ? |        8 |   479923553 |
|        242 | 284 | 5PDRR5YY ? |        8 |   658551160 |
|        283 | 282 | NWZSPCZZ ? |        8 |   802197234 |
|        114 | 233 | 3IHU1YKK ? |        9 |   118146688 |
|         85 | 226 | BQTD6A44 ? |        9 |   292147382 |
|        255 | 291 | YNT0YN11 ? |        9 |   559911752 |
|        281 | 258 | IHK46577 ? |       10 |   196799301 |
|        198 | 263 | SHEVXPBB ? |       10 |   545833955 |
|         11 | 298 | QEHHSAEE ? |       10 |  1087006220 |
|         60 | 250 | GEH9PVBB ? |       11 |   225193615 |
|        270 | 272 | 3YY2EMM ?  |       11 |  1143544695 |
|        300 | 290 | O8B4WRCC ? |       11 |  1209867696 |
|         68 | 246 | FTEEVJ00 ? |       12 |   608441021 |
|        177 | 244 | X1JYHUBB ? |       12 |  1192309224 |
|        208 | 240 | W771N588 ? |       12 |  1347800170 |
|        249 | 300 | 97V6UXYY ? |       13 |   500846709 |
|        100 | 292 | 71JZTMQQ ? |       13 |   607266604 |
|         38 | 283 | WBQ0DGLL ? |       13 |  1187952919 |
|         87 | 235 | MBPPXJ44 ? |       14 |   327445349 |
|        178 | 242 | EZ1ET3ZZ ? |       14 |   852480639 |
|         62 | 281 | EIHVH3ZZ ? |       14 |  1420114728 |
|         95 | 251 | PT8E2CII ? |       15 |    72502146 |
|        170 | 253 | 8L35PFYY ? |       15 |  1091765038 |
|         65 | 287 | X5ZR3LHH ? |       15 |  1405199431 |
|        138 | 278 | MBW03OUU ? |       16 |    84358922 |
|          7 | 268 | 2G4G42WW ? |       16 |  1257379717 |
|          1 | 299 | ZT8QRRMM ? |       16 |  1327297420 |
|        221 | 297 | H00HLNRR ? |       17 |   890140945 |
|         40 | 204 | O7VUW2NN ? |       17 |  1238474743 |
|        122 | 229 | 3XEZG0PP ? |       17 |  1359337651 |
+------------+-----+------------+----------+-------------+
51 rows in set (0.01 sec)

再次运行以供下一个用户或同一用户使用：

update random set randomOrder=rand()*unix_timestamp();
truncate table questions_for_user;
insert into questions_for_user (id,question,category,randomOrder)
select id,question,category,randomOrder from random order by rand();

select r.* 
FROM questions_for_user r 
WHERE 
( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 
ORDER by r.category ASC, randomOrder;

+------------+-----+------------+----------+-------------+
| physcOrder | id  | question   | category | randomOrder |
+------------+-----+------------+----------+-------------+
|        112 | 286 | ORK15577 ? |        1 |   193281314 |
|        176 | 266 | JNQH26DD ? |        1 |   530153269 |
|        296 | 267 | T2HPRW88 ? |        1 |   891041924 |
|         37 | 293 | GFWQ0BEE ? |        2 |   222852606 |
|        239 | 285 | E3KDJ144 ? |        2 |   679635152 |
|        262 | 275 | U5BFCUFF ? |        2 |   846163956 |
|         95 | 288 | 6GGOI2CC ? |        3 |  1244253481 |
|         81 | 231 | LQMYMV44 ? |        3 |  1380298624 |
|         59 | 296 | 8MJCWR55 ? |        3 |  1420850554 |
|         28 | 295 | Y85VNBWW ? |        4 |   806083444 |
|         30 | 289 | FUODJHJJ ? |        4 |   814854070 |
|         34 | 280 | 441X1YTT ? |        4 |  1119188021 |
|        232 | 234 | DWZULKBB ? |        5 |   296692881 |
|        295 | 273 | CC5FGKFF ? |        5 |   337915901 |
|        140 | 252 | Y7V58ZEE ? |        5 |   644987638 |
|        257 | 271 | TNG23R11 ? |        6 |   619359840 |
|         27 | 294 | PTEMZ7GG ? |        6 |   869855570 |
|        218 | 276 | 3J1KGJZZ ? |        6 |  1390090875 |
|         64 | 279 | VQN6TVSS ? |        7 |    33942495 |
|        290 | 277 | DXE1W0GG ? |        7 |   100007602 |
|        173 | 274 | LQTFS7RR ? |        7 |   946909650 |
|        291 | 265 | A99UU1YY ? |        8 |   107468716 |
|         49 | 284 | 5PDRR5YY ? |        8 |  1068298164 |
|        228 | 282 | NWZSPCZZ ? |        8 |  1115906220 |
|        284 | 226 | BQTD6A44 ? |        9 |    45678738 |
|        179 | 291 | YNT0YN11 ? |        9 |   700305900 |
|        164 | 233 | 3IHU1YKK ? |        9 |  1416089612 |
|        193 | 258 | IHK46577 ? |       10 |   460111512 |
|        214 | 298 | QEHHSAEE ? |       10 |   482322673 |
|          7 | 263 | SHEVXPBB ? |       10 |   766681927 |
|        178 | 290 | O8B4WRCC ? |       11 |   341509950 |
|         31 | 272 | 3YY2EMM ?  |       11 |   726662739 |
|        297 | 250 | GEH9PVBB ? |       11 |  1386568968 |
|         32 | 240 | W771N588 ? |       12 |   303493686 |
|        283 | 246 | FTEEVJ00 ? |       12 |   710591266 |
|        177 | 244 | X1JYHUBB ? |       12 |   916685336 |
|        212 | 283 | WBQ0DGLL ? |       13 |   595739692 |
|        159 | 300 | 97V6UXYY ? |       13 |   688431139 |
|         52 | 292 | 71JZTMQQ ? |       13 |  1039681379 |
|         56 | 235 | MBPPXJ44 ? |       14 |   109832248 |
|        207 | 242 | EZ1ET3ZZ ? |       14 |   418951740 |
|          6 | 281 | EIHVH3ZZ ? |       14 |  1182157711 |
|        149 | 287 | X5ZR3LHH ? |       15 |   364819476 |
|         88 | 251 | PT8E2CII ? |       15 |   673475236 |
|        123 | 253 | 8L35PFYY ? |       15 |  1204512525 |
|        190 | 278 | MBW03OUU ? |       16 |   641720378 |
|        160 | 299 | ZT8QRRMM ? |       16 |  1289470813 |
|         75 | 268 | 2G4G42WW ? |       16 |  1427431541 |
|        202 | 297 | H00HLNRR ? |       17 |   374047531 |
|        243 | 204 | O7VUW2NN ? |       17 |   951026810 |
|        300 | 229 | 3XEZG0PP ? |       17 |  1319302198 |
+------------+-----+------------+----------+-------------+
51 rows in set (0.01 sec)

所以问题是不同的。

以随机顺序获取最后的结果：

select r.* 
FROM questions_for_user r 
WHERE 
( SELECT COUNT(*) FROM random r1 WHERE r.category = r1.category AND r.id < r1.id ) <= 2 
ORDER by randomOrder;

+------------+-----+------------+----------+-------------+
| physcOrder | id  | question   | category | randomOrder |
+------------+-----+------------+----------+-------------+
|         64 | 279 | VQN6TVSS ? |        7 |    33942495 |
|        284 | 226 | BQTD6A44 ? |        9 |    45678738 |
|        290 | 277 | DXE1W0GG ? |        7 |   100007602 |
|        291 | 265 | A99UU1YY ? |        8 |   107468716 |
|         56 | 235 | MBPPXJ44 ? |       14 |   109832248 |
|        112 | 286 | ORK15577 ? |        1 |   193281314 |
|         37 | 293 | GFWQ0BEE ? |        2 |   222852606 |
|        232 | 234 | DWZULKBB ? |        5 |   296692881 |
|         32 | 240 | W771N588 ? |       12 |   303493686 |
|        295 | 273 | CC5FGKFF ? |        5 |   337915901 |
|        178 | 290 | O8B4WRCC ? |       11 |   341509950 |
|        149 | 287 | X5ZR3LHH ? |       15 |   364819476 |
|        202 | 297 | H00HLNRR ? |       17 |   374047531 |
|        207 | 242 | EZ1ET3ZZ ? |       14 |   418951740 |
|        193 | 258 | IHK46577 ? |       10 |   460111512 |
|        214 | 298 | QEHHSAEE ? |       10 |   482322673 |
|        176 | 266 | JNQH26DD ? |        1 |   530153269 |
|        212 | 283 | WBQ0DGLL ? |       13 |   595739692 |
|        257 | 271 | TNG23R11 ? |        6 |   619359840 |
|        190 | 278 | MBW03OUU ? |       16 |   641720378 |
|        140 | 252 | Y7V58ZEE ? |        5 |   644987638 |
|         88 | 251 | PT8E2CII ? |       15 |   673475236 |
|        239 | 285 | E3KDJ144 ? |        2 |   679635152 |
|        159 | 300 | 97V6UXYY ? |       13 |   688431139 |
|        179 | 291 | YNT0YN11 ? |        9 |   700305900 |
|        283 | 246 | FTEEVJ00 ? |       12 |   710591266 |
|         31 | 272 | 3YY2EMM ?  |       11 |   726662739 |
|          7 | 263 | SHEVXPBB ? |       10 |   766681927 |
|         28 | 295 | Y85VNBWW ? |        4 |   806083444 |
|         30 | 289 | FUODJHJJ ? |        4 |   814854070 |
|        262 | 275 | U5BFCUFF ? |        2 |   846163956 |
|         27 | 294 | PTEMZ7GG ? |        6 |   869855570 |
|        296 | 267 | T2HPRW88 ? |        1 |   891041924 |
|        177 | 244 | X1JYHUBB ? |       12 |   916685336 |
|        173 | 274 | LQTFS7RR ? |        7 |   946909650 |
|        243 | 204 | O7VUW2NN ? |       17 |   951026810 |
|         52 | 292 | 71JZTMQQ ? |       13 |  1039681379 |
|         49 | 284 | 5PDRR5YY ? |        8 |  1068298164 |
|        228 | 282 | NWZSPCZZ ? |        8 |  1115906220 |
|         34 | 280 | 441X1YTT ? |        4 |  1119188021 |
|          6 | 281 | EIHVH3ZZ ? |       14 |  1182157711 |
|        123 | 253 | 8L35PFYY ? |       15 |  1204512525 |
|         95 | 288 | 6GGOI2CC ? |        3 |  1244253481 |
|        160 | 299 | ZT8QRRMM ? |       16 |  1289470813 |
|        300 | 229 | 3XEZG0PP ? |       17 |  1319302198 |
|         81 | 231 | LQMYMV44 ? |        3 |  1380298624 |
|        297 | 250 | GEH9PVBB ? |       11 |  1386568968 |
|        218 | 276 | 3J1KGJZZ ? |        6 |  1390090875 |
|        164 | 233 | 3IHU1YKK ? |        9 |  1416089612 |
|         59 | 296 | 8MJCWR55 ? |        3 |  1420850554 |
|         75 | 268 | 2G4G42WW ? |       16 |  1427431541 |
+------------+-----+------------+----------+-------------+
51 rows in set (0.01 sec)

其他答案似乎返回相同的问题（前三或后三），我认为是由于表的物理排序，这就是我创建第二个表的原因。第二个表使用一个新的虚假物理排序，插入是通过随机排序（因此不存在物理排序问题）。希望这有意义。

当然，我从pjanaway那里抄袭了答案，但我无法在我的系统上使Gordon的方法工作，尽管我喜欢他的变量方法。

祝好运。

- Drew

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- pjau · Accepted Answer

7

除了其他答案，这也是另一种实现的方式。

SELECT r.* FROM random r
WHERE (
  SELECT COUNT(*) FROM random r1
  WHERE r.category = r1.category AND r.id < r1.id
) <= 2
ORDER BY r.category ASC, RAND()

- pjau

我无法让Gordon的工作，但我可以让这个工作。将显示一个pastie。 - Drew

@DrewPierce 这段代码更加紧凑，我们已经更新了Gordon的答案，你试过最新的更新了吗？ - Maytham Fahmi

是的，我尝试了三次，我知道它已经更新了，但我会再试一次。 - Drew

好的，pjanaway的结果如预期一样，17个类别，每个类别3个，共51行。 - Drew

由于内部查询第四列“seqnum”的90％= 1，10％= 2（左右），Gordon的所有300行都会出现问题，并且即使进行了多次编辑测试也会出现问题。 - Drew

让我们在聊天中继续这个讨论。 - Maytham Fahmi