根据位置和位置按顺序随机2条记录 [英] random 2 records according to position and order by position

问题描述

question_tbl: q_id(int)PK，位置(int)，q_type(varchar(255))，问题(varchar(255))，级别(int)

question_tbl: q_id (int) PK, position (int), q_type (varchar(255)), question (varchar(255)), level (int)

我尝试过的查询:

SELECT q_id, position, q_type, question, level
FROM (SELECT * FROM tbl_question ORDER BY rand()) a
WHERE level = '1'
ORDER BY position

得到以下结果:

q_id    position    qtn     level
1           1       demo      1
2           1       demo12    1
3           1       demo21    1
4           2       demo22    1
5           2       demo11    1
6           2       demo31    1
7           3       demo41    1
8           3       demo51    1
9           4       demo61    1
10          1       demo71    1
11          1       demo81    1
12          2       demo91    1
13          3       demo511   1
14          4       demo514   1
15          4       demo4511  1

预期结果: 根据位置和位置按顺序随机记录2条记录，如下所示:

Expected result: random 2 records according to position and order by position as below:

q_id    position    qtn     level
2           1       demo12    1
3           1       demo21    1
4           2       demo22    1
5           2       demo11    1
7           3       demo41    1
8           3       demo51    1
9           4       demo61    1
15          4       demo4511  1

希望按位置随机记录2条记录

Want randome 2 records by position

推荐答案

您可以按position, rand()排序，以便在每个position分区中具有随机顺序.然后使用变量枚举position分区的记录:

You can sort by position, rand() so as to have a random order within each position partition. Then use variable to enumerate records of position partitions:

SELECT q_id, position, qtn, level, rn
FROM (
  SELECT q_id, position, qtn, level,
           @rn := IF(@pos = position, @rn + 1,
                   IF(@pos := position, 1, 1)) AS rn
  FROM (
    SELECT *
    FROM tbl_question     
    WHERE level = '1'
    ORDER BY position, rand() ) a
  CROSS JOIN (SELECT @rn := 0, @pos := 0) AS vars ) b
WHERE b.rn <= 2  
ORDER BY position

外部查询使用从每个position分区中仅选择两个记录.

The outer query uses @rn to select just two records from each position partition.

此处演示

这篇关于根据位置和位置按顺序随机2条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！