需求
从题库中随机抽取相应数量的各个类型的题目组成试卷
数据库表
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()