随机抽题业务场景实现

需求

从题库中随机抽取相应数量的各个类型的题目组成试卷

数据库表

CREATE TABLE `question` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '题目id',
  `question_type` int(20) DEFAULT NULL COMMENT '题目类型：1单选，2多选，3判断，4填空，5连线。6排序，7简答，8完型',
  `stem` text COMMENT '题干',
  `options` json DEFAULT NULL COMMENT '选项',
  `correct_answer` json DEFAULT NULL COMMENT '答案',
  `analysis` text COMMENT '问题解析',
  `difficulty` int(10) DEFAULT NULL COMMENT '难度：1易，2中，3难',
  `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人姓名',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_user` bigint(20) DEFAULT NULL,
  `update_user` bigint(20) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='题目表';

实现

假设数据很多的情况下单选抽20道，填空10道，简答题5道
主要是sql实现，其他的代码省略

 SELECT id,stem,options,question_type questionType
        FROM (
        SELECT id,stem,options,question_type,
        @row_number := IF(@prev_type = question_type, @row_number + 1, 1) AS row_number,
        @prev_type := question_type
        FROM (
        SELECT id,stem,options,question_type
        FROM question
        ORDER BY question_type, RAND()
        ) t1,
        (SELECT @row_number := 0, @prev_type := '') t2
        ) t3
		where (
				case question_type 
				when 1 then row_number<=20
				when 4 then row_number<=10
				when 7 then row_number<=5
				else row_number<=0
				end
		)
        ORDER BY question_type, RAND()