如何通过在MySql上过滤多个列来限制多个数字? [英] How to limit multiple numbers by filtering multiple columns on MySql?

问题描述

我想从数据库中获得4个不同类别的50个问题.我想要从4个不同类别中选择不同数量的问题；我的结果集必须包含第一类12个问题，第二类20个问题，第三类10和第四类8个问题.我的问题表中总共有50个问题. 我为此使用了 LIMIT 函数.但是当我有不止一列的时候，我很困惑.

I would like to get 50 questions from 4 different categories from the database. I want a different number of questions from each of the 4 different categories; my result set must contain first category 12 questions, second category 20 questions, third 10, and fourth category 8 questions. In total 50 question from my question table. I used the LIMIT function for this. But when I have more than one column, I'm confused.

这是我的桌子:

 | category_id| question-text| Col3 |
 |------------|--------------|------|
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |
 |    1       |  sample      |  ieb |
 |    2       |  sample      |  aem |
 |    3       |  sample      |  atd |
 |    4       |  sample      |  oui |

我需要这个结果: 类别1中有5个问题. 类别2中的3个问题. 类别3中的2个问题. 类别4中有7个问题.

And I need this result: 5 questions from category # 1. 3 questions from category # 2. 2 questions from category # 3. 7 questions from category # 4.

推荐答案

您可以使用 UNION 查询可一次获得所有问题:

You can use a UNION query to get all your questions at once:

(SELECT * FROM question WHERE categori_id=1 ORDER BY RAND() LIMIT 12)
UNION
(SELECT * FROM question WHERE categori_id=2 ORDER BY RAND() LIMIT 20)
UNION
(SELECT * FROM question WHERE categori_id=3 ORDER BY RAND() LIMIT 10)
UNION
(SELECT * FROM question WHERE categori_id=4 ORDER BY RAND() LIMIT 8)

这篇关于如何通过在MySql上过滤多个列来限制多个数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！