sql mysql查询，根据条件随机获取已定义的行数

I am developing a Online Exam Application where I have a question master table which contains a field ( name : qtype ) containing the data for the type of question. A question type can be either of following three types :

我正在开发一个在线考试应用程序，其中有一个问题主表，其中包含一个字段(名称:qtype)，其中包含问题类型的数据。问题类型可以是以下三种类型之一:

single
单
multiple
多个
desc
desc

The application is generating a random question paper from this master table by using the following query :

应用程序使用以下查询从这个主表生成一个随机的问题文件:

select * from mst_question where test_id = 1 ORDER BY RAND() LIMIT 25

This generates a random questionnaire of 25 questions for my Online Exam Application.

这将为我的在线考试申请生成一份包含25个问题的随机问卷。

So far Its working good...

到目前为止，它运行良好……

Now, I need to implement a feature in my application where the 25 randomly generated questions ( or whatever number - this will depend on the test id ) will always have a FIXED mix of the different types of questions available in the master question table ( mst_question ) for every randomly generated questionnaire set.

现在,我在我的应用程序需要实现一个功能的25个随机生成问题(或其他数字——这将取决于测试id)总有一个固定的组合不同类型的问题可以在主问题表(mst_question)为每一个随机生成的问卷。

Say, if master question table is having 108 questions for a particular test id and all the three types of questions are in the db for this test , it will provide same number of different types questions for each random query !! I have written the following sql query to find out the percentage of each type of question in the master question table.

例如，如果主问题表对一个特定的测试id有108个问题，并且这三种类型的问题都在这个测试的db中，那么它将为每个随机查询提供相同数量的不同类型的问题!我编写了下面的sql查询，以找出主问题表中每种类型问题的百分比。

So far i have tried this and came up with this sql query :

到目前为止，我已经尝试过这个方法，并提出了这个sql查询:

select qtype,count(*) as qtypetotal,(select count(*) from mst_question where test_id = 1) as totalqtype,round((count(*)/(select count(*) from mst_question where test_id = 1)*100),2) as qtypepercentage from mst_question where test_id = 1 group by qtype

The output of above query is :

上述查询的输出为:

qtype       qtypetotal  totalqtype  qtypepercentage  
desc        24      108     22.22 %  
multiple    34      108     31.48 %  
single      50      108     46.30 %

I need to form a sql query which will give me 25 randomly generated questions where 22.22% of 25 questions should be desc type , 31.48 % of 25 questions should be multiple type and remaining 46.30 % of 25 questions should be single type.

我需要形成一个sql查询，它会给我25个随机生成的问题，其中22.22%的25个问题应该是desc类型，31.48%的25个问题应该是多类型的，剩下的46.30%的25个问题应该是单一类型的。

I am stuck ....... Pls advise...

我困.......请建议……

Thanks in advance :-)

提前谢谢:-)

Thanks @MikeBrant.... I have generated the dynamic sql which is definitely the way i wanted... just one issue now.... if i execute this sql query :

由于@MikeBrant ....我生成了动态sql，这绝对是我想要的方式……现在只有一个问题....如果我执行这个sql查询:

select qtype,round(25*(count(*)/(select count(*) from mst_question where test_id = 1))) as numquests from mst_question where test_id = 1 group by qtype

I am getting the following results :

我得到了如下结果:

"desc" "6" "multiple" "8" "single" "12"

"desc" "6" "倍数" "8"单数"12"

and based on the above query i generate this dynamic query :

基于上面的查询，我生成了这个动态查询:

$dynamicsql[] =  "(SELECT * FROM mst_question WHERE test_id = 1 AND qtype = '".trim($rstype->qtype)."' ORDER BY RAND() LIMIT ".$rstype->numquests.")";

$finalsql = implode(" UNION ALL ",$dynamicsql)." ORDER BY RAND()";

I want to generate a total of 25 random questions but the sum of these qtypes is 26 !!! and i am getting a question extra :-(

我想生成总共25个随机问题，但这些qtype的总和是26 !!我还有一个问题:

1 个解决方案

#1

I would probably just use a UNION here:

我可能会在这里使用一个联合:

    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'desc'
    ORDER BY RAND() LIMIT X)
UNION ALL
    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'multiple'
    ORDER BY RAND() LIMIT Y)
UNION ALL
    (SELECT * FROM mst_question
    WHERE test_id = 1 AND qtype = 'single'
    ORDER BY RAND() LIMIT Z)
ORDER BY RAND()

You can use a query like you posed on your original question to get the values for X, Y, and Z to be used.

您可以使用一个查询，就像您在最初的问题中提出的那样，以获取要使用的X、Y和Z的值。

#1