使用单个 sql 查询选择多组行

发布于 2024-12-11 15:13:55 字数 570 浏览 0 评论 0原文

我有一张问题表，如下所示

。问题|答案|教学大纲 |难度

我想创建一个 SQL 语句，当难度很简单时，该语句为每个不同的教学大纲随机选择 5 个问题。

所以如果有 4 个教学大纲，我会有 20 个问题。

我在想这样的事情......

   SELECT 
    * 
FROM 
    questions 
WHERE 
    difficulty='easy' 
AND 
    syllabus 
IN 
(
    SELECT DISTINCT 
        syllabus 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
) 
LIMIT 
(5*
    (
    SELECT 
        COUNT(DISTINCT syllabus) 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
    )

但这并没有从每个不同的教学大纲中返回 5 个，而是从任何教学大纲中返回正确数量的问题。

原文

I have a table of questions set out like so..

id | question | answer | syllabus | difficulty

I want to create an SQL statement that selects 5 questions at random for each of the distinct syllabuses when the difficulty is easy.

So if there are 4 syllabuses I would have 20 questions.

I was thinking something like this...

   SELECT 
    * 
FROM 
    questions 
WHERE 
    difficulty='easy' 
AND 
    syllabus 
IN 
(
    SELECT DISTINCT 
        syllabus 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
) 
LIMIT 
(5*
    (
    SELECT 
        COUNT(DISTINCT syllabus) 
    FROM 
        questions 
    WHERE 
        difficulty='easy'
    )

But this doesn't return 5 from each of the distinct syllabuses only the correct number of questions from any syllabus.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静谧 2024-12-18 15:13:55

这可行，但速度非常慢：

SELECT * FROM questions WHERE difficulty='easy' ORDER BY RAND() LIMIT 5;

更好的方法是首先选择 5 个 ID，然后检索与这些 ID 对应的行。可以使用 WHERE ID > 选择 ID 本身。 RAND(0,MAX(ID))，但如果存在间隙，那么您的数据就会出现偏差。

这里讨论了更好的替代方案，但需要更多的努力：Simple Random Samples from a Sql数据库

This would work, but would be really slow:

SELECT * FROM questions WHERE difficulty='easy' ORDER BY RAND() LIMIT 5;

A better way is to select 5 ID's first, and then retrieve the rows corresponding to those ID's. Selecting the ID's themselves can be done with a WHERE ID > RAND(0,MAX(ID)), but if there are gaps, then your data will be skewed.

A better alternative is discussed here, but requires more effort: Simple Random Samples from a Sql database

回复收藏 0 原文

~没有更多了~