如何从MySQL中的每个类别获取RANDOM记录? [英] How to get RANDOM records from each category in MySQL?
问题描述
在我的MySQL数据库中，我有一个表，其中包含不同类别的不同问题.
In my MySQL database, I have a table with different questions in different categories.
我想编写一条SQL语句，该语句返回3个EACH类别的RANDOM问题.
I would like to write a SQL statement that returns 3 RANDOM questions of EACH category.
以下是数据库记录的示例:
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
到目前为止，我一直在使用以下语句进行测试:
I have so far played with the following statement for testing:
SELECT * FROM `random` ORDER BY RAND() LIMIT 0,3;
这仅返回来自所有类别的3个RANDOM问题.
This return only 3 RANDOM questions from all categories.
之后，我查看了此链接的示例: MYSQL从每个类别中随机选择
And I have afterwards looked for example at this link: 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)
但是在这里，我需要手动添加每个类别.
But here I need to add each category manually.
我的问题:我想知道是否有可能(自动)从所有类别的每个类别中提取3个RANDOM记录/行吗?
My Question: I was a wonder if it is at all possible to fetch 3 RANDOM records/rows from each category of all categories (automatically)?
编辑
这不是问题的一部分，而是帮助.
This is not part of the question but help.
虚拟数据创建者
查询代码将在名为random的表中创建一个名为create_random的存储过程，当您运行该存储过程时，它将在一个随机表中创建随机虚拟数据:
Dummy data creator
The query code will table called random and created a stored procedure called create_random and when you run the stored procedure, it will create random dummy data inside a random table:
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答案仅从前3个或后3个问题中选择RANDOM，我检查了Gordon答案是因为他首先回答了问题，但这并不意味着其他答案都不好，所有答案都很好，这取决于用户选择正确的答案或答案的组合.我喜欢所有答案，并将其投票.德鲁·皮尔斯(Drew Pierce)对这个问题进行了新的回答，现在并且几乎接近目标，这更加有趣.谢谢大家.
Note: I tried all answers and all works fine. Gordon Linoff and pjanaway answer select RANDOM only from top 3 or bottom 3 questions, I have checked Gordon answer because he answered first, but that does not mean other answers is not good, all of them are good and it is up to users to pick the right answer or combination of answers. I love all the answers and vote them up. Drew Pierce answer newly to this question, it is more interesting right now and almost near the goal. Thanks to all.
推荐答案
是的，您可以通过枚举行然后获取前三行来实现:
Yes, you can do this by enumerating the rows and then fetching the top three:
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;
这篇关于如何从MySQL中的每个类别获取RANDOM记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
